Import from Excel with IMEX=1 still gives probs

Hi:

Am trying to import XLS data into SQL 2005 SP2 thro a SSIS Data Flow task. My Excel Connection string has IMEX=1, ImportMixedTypes is set to Text and the typeguessrows is set to 0.

Import works fine for cells of Format Text, but when I have a large number (in a general Format cell) it gets converted into scientific notation(e.g. 3.234175e+7) in the table.

What am I doing wrong?

TIA

Kar

What's the type of the column in the data flow?|||

The column is of type DT_WSTR(255) in the data flow. There's no prob with cells that are textual, or are numeric with Text Format. Am only having a prob with cells with General Format I think. These cells look normal in excel, but when I import to Ole DB, I get Scientific Notation.

I have tried to save Excel as Text, and that works, but that will introduce unnecessary additional layers to build, test and maintain. Hope to find a solution within the box.

TIA

Kar

import flat file

I am trying to import a flat file using SQL Server Management Studio and am receiveing the error:

Error 0xc0202055: Data Flow Task: The column delimiter for column "Column 19" was not found.
(SQL Server Import and Export Wizard)

I would like to capture the rows that are causing the error and have the import continue. Am I able to edit the behavior somehow?

Thanks.

If I recall correctly; in one of the last steps of the import wizard you are asked to either execute the package immediately or to save it as a .dtsx file.

Chose to save it as a .dtsx files and the use BIDS to edit it according to your needs.

|||

Hi Rafael, thanks for responding. I get to the screen where it tells me that ' The package will be saved to the instance of the SQL Server Database Engine ...', but it doesn't allow me to choose the file extension. Yes, I'm very new at this.

Thank you again, I really appreciate your help.

|||

Hi, I was able to save the .dtsx file. Now on to BIDS.

Thanks.

|||

I've been trying to edit the package in BIDS but am not having success. I'm importing a flat file and am trying to write the records that do not get imported successfully to an error file. If anyone can help me out.....

Thanks.

|||

tantz wrote:

I've been trying to edit the package in BIDS but am not having success. I'm importing a flat file and am trying to write the records that do not get imported successfully to an error file. If anyone can help me out.....

Thanks.

You will have to tell us what you are not having success with first.|||

Well, I read that you can 'use the Error Output Page of the Flat File Source Editor dialog box to select error-handling options'. How do I do this? Thanks.

|||

tantz wrote:

Well, I read that you can 'use the Error Output Page of the Flat File Source Editor dialog box to select error-handling options'. How do I do this? Thanks.

On the data flow, you grab the red arrow of the flat file source and connect it to a destination (flat file, sql server, etc...)|||

Thanks Phil. I was able to connect it to a flat file, and am getting the same errors. Where can we find this file with the errors? Also, is there a way to make the package continue processing even when there are errors?

I really appreciate your time and help.

|||

Open the Flat file Source editor; go to the error output page and make sure all the values in Error and truncation columns are 'Redirect Row'. Tip You can Shift+ Select al of them and then use the 'Set this value to the selected Cells'. After doing that, make sure you connect the red arrow to a file or any other destination; so you can examine it later.

|||Hi, thanks for your reply.
I don't think I'm looking in the right place - can you tell me exactly how you're getting to the error output page. Thanks so much.|||When you double click on the flat file source, there is a button, "error output" or something like that.|||

Cool, I got it, but I still am not able to see the records. I've connected the flat file source with a red arrow 'flat file source error output'. What else am I missing? Anything with mappings? An error list is displayed at the bottom, but it has 0 errors, 0 warnings, 0 messages, but I know I am getting errors.

Thanks so much.

|||

Hi, I think I've worked out the error output, but I seem to have created a different problem. I am now getting a message 'cannot open the data file. the process cannot access the file because it is being used by another process'.

It's a .txt file. Nothing else is using it. I just ran something in SQL Server Management Studio using the same file and it works fine. Is it possible that I changed something in the .dtsx package when I was trying to get the error output to work?

If anyone can help me out -
Thanks.

IMPORT DBASE IV (DBF) FILE TO SQL SERVER 2005

Hi,
I am wondering if anyone knows of a reliable tool for importing Foxpro
(dbase) (dbf) files to SQL Server?
I have inherted this task from a former employee. I have been using an
Access database with an ODBC link to SQL Server for the import task -
and it worked fine until last week. For some reason, it just quit
importing one of the dbase files.
So, I tried use "db workbench" to convert the dbase file to a text file
first, then tried to import it into Access. It apparently "worked", but
the data got totally corrupted in the process.
Now, I am back at square one - and I need a tool I can use to either
1.) import the dbase file directly to SQL Server, or 2.) a reliable
tool for converting the dbase file to a text file, csv, or xls file for
importing into SQL Server.
Any ideas/suggestions greatly appreciated!
Thanks much
CORRECTION: I used "DBF Viewer", not "db workbench" to convert to text
file
tootsu...@.gmail.com wrote:
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much
|||I have the same issue, and found that the driver for dbf files is not
included in the sql2005 install. I had to go to msdn.microsoft.com to get
the foxpro driver files and installed it. I can now at least locate the dbf
and attempt the import, but it is only importing the first 20 rows of 16,000
set. Interested to see what other answers you get becasue I could get only
one person even attempting to help me, and she got me as far as this.
<tootsuite@.gmail.com> wrote in message
news:1158866292.228863.307680@.h48g2000cwc.googlegr oups.com...
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much
>
|||Update - I just discovered the *easiest* way to do this! You can open
DBF files with Microsoft Excel - then just save as an xls - then import
- viola - works!
Don't know why I didn't discover this earlier. No need for any special
tools, or dts.
JC HARRIS wrote:[vbcol=seagreen]
> I have the same issue, and found that the driver for dbf files is not
> included in the sql2005 install. I had to go to msdn.microsoft.com to get
> the foxpro driver files and installed it. I can now at least locate the dbf
> and attempt the import, but it is only importing the first 20 rows of 16,000
> set. Interested to see what other answers you get becasue I could get only
> one person even attempting to help me, and she got me as far as this.
>
> <tootsuite@.gmail.com> wrote in message
> news:1158866292.228863.307680@.h48g2000cwc.googlegr oups.com...
|||Hi!
Yes, older format DBFs can be opened with Excel but you will lose the
content of any Memo fields. As an alternative you can download and install
the FoxPro and Visual FoxPro OLE DB data provider from
msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
Import Wizard or set up a linked server.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158874680.304855.200110@.h48g2000cwc.googlegr oups.com...
> Update - I just discovered the *easiest* way to do this! You can open
> DBF files with Microsoft Excel - then just save as an xls - then import
> - viola - works!
[vbcol=seagreen]
|||What Cindy says is correct. I could not use the excel method because of the
dbf size (overflows the excel program). I followed Cindy's instrcution on
another newsgroup and it worked great.
"Cindy Winegarden" <cindy@.cindywinegarden.com> wrote in message
news:OwD1qPl3GHA.4924@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegr oups.com...
>
>
|||Hi Cindy,
Thanks for the information. I don't know if I have any memo fields -
how can I tell?
Would the column just show up as blank in Excel?
Thanks!
Cindy Winegarden wrote:[vbcol=seagreen]
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegr oups.com...
|||Hi Cindy,
Yes, I see what you mean - the memo fields are blank in Excel.
I went to the link you listed below, but I don't know which file to
download? They all seem like service packs. Is this the right page?
Help.
THANKS
Cindy Winegarden wrote:[vbcol=seagreen]
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegr oups.com...
|||"JC HARRIS" <harris1113@.fake.com> wrote in message
news:%230YkfWm3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> What Cindy says is correct. I could not use the excel method because of
the
> dbf size (overflows the excel program). I followed Cindy's instrcution on
> another newsgroup and it worked great.
If you have it available, you might the Office 2007 Beta version of Excel.
It increases the number of records immensely.
Jonathan
|||Hi!
http://msdn.microsoft.com/vfoxpro/downloads/updates/ , second item. It
points to
http://www.microsoft.com/downloads/d...displaylang=en .
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158946224.807292.64180@.m7g2000cwm.googlegrou ps.com...
> Hi Cindy,
> Yes, I see what you mean - the memo fields are blank in Excel.
> I went to the link you listed below, but I don't know which file to
> download? They all seem like service packs. Is this the right page?
> Help.
> THANKS
>
> Cindy Winegarden wrote:
>

IMPORT DBASE IV (DBF) FILE TO SQL SERVER 2005

Hi,
I am wondering if anyone knows of a reliable tool for importing Foxpro
(dbase) (dbf) files to SQL Server?
I have inherted this task from a former employee. I have been using an
Access database with an ODBC link to SQL Server for the import task -
and it worked fine until last week. For some reason, it just quit
importing one of the dbase files.
So, I tried use "db workbench" to convert the dbase file to a text file
first, then tried to import it into Access. It apparently "worked", but
the data got totally corrupted in the process.
Now, I am back at square one - and I need a tool I can use to either
1.) import the dbase file directly to SQL Server, or 2.) a reliable
tool for converting the dbase file to a text file, csv, or xls file for
importing into SQL Server.
Any ideas/suggestions greatly appreciated!
Thanks muchCORRECTION: I used "DBF Viewer", not "db workbench" to convert to text
file
tootsu...@.gmail.com wrote:
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much|||I have the same issue, and found that the driver for dbf files is not
included in the sql2005 install. I had to go to msdn.microsoft.com to get
the foxpro driver files and installed it. I can now at least locate the dbf
and attempt the import, but it is only importing the first 20 rows of 16,000
set. Interested to see what other answers you get becasue I could get only
one person even attempting to help me, and she got me as far as this.
<tootsuite@.gmail.com> wrote in message
news:1158866292.228863.307680@.h48g2000cwc.googlegroups.com...
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much
>|||Update - I just discovered the *easiest* way to do this! You can open
DBF files with Microsoft Excel - then just save as an xls - then import
- viola - works!
Don't know why I didn't discover this earlier. No need for any special
tools, or dts.
JC HARRIS wrote:
> I have the same issue, and found that the driver for dbf files is not
> included in the sql2005 install. I had to go to msdn.microsoft.com to get
> the foxpro driver files and installed it. I can now at least locate the dbf
> and attempt the import, but it is only importing the first 20 rows of 16,000
> set. Interested to see what other answers you get becasue I could get only
> one person even attempting to help me, and she got me as far as this.
>
> <tootsuite@.gmail.com> wrote in message
> news:1158866292.228863.307680@.h48g2000cwc.googlegroups.com...
> > Hi,
> >
> > I am wondering if anyone knows of a reliable tool for importing Foxpro
> > (dbase) (dbf) files to SQL Server?
> >
> > I have inherted this task from a former employee. I have been using an
> > Access database with an ODBC link to SQL Server for the import task -
> > and it worked fine until last week. For some reason, it just quit
> > importing one of the dbase files.
> >
> > So, I tried use "db workbench" to convert the dbase file to a text file
> > first, then tried to import it into Access. It apparently "worked", but
> > the data got totally corrupted in the process.
> >
> > Now, I am back at square one - and I need a tool I can use to either
> > 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> > tool for converting the dbase file to a text file, csv, or xls file for
> > importing into SQL Server.
> >
> > Any ideas/suggestions greatly appreciated!
> >
> > Thanks much
> >|||Hi!
Yes, older format DBFs can be opened with Excel but you will lose the
content of any Memo fields. As an alternative you can download and install
the FoxPro and Visual FoxPro OLE DB data provider from
msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
Import Wizard or set up a linked server.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
> Update - I just discovered the *easiest* way to do this! You can open
> DBF files with Microsoft Excel - then just save as an xls - then import
> - viola - works!
>> > I am wondering if anyone knows of a reliable tool for importing Foxpro
>> > (dbase) (dbf) files to SQL Server? ...|||What Cindy says is correct. I could not use the excel method because of the
dbf size (overflows the excel program). I followed Cindy's instrcution on
another newsgroup and it worked great.
"Cindy Winegarden" <cindy@.cindywinegarden.com> wrote in message
news:OwD1qPl3GHA.4924@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
>> Update - I just discovered the *easiest* way to do this! You can open
>> DBF files with Microsoft Excel - then just save as an xls - then import
>> - viola - works!
>> > I am wondering if anyone knows of a reliable tool for importing Foxpro
>> > (dbase) (dbf) files to SQL Server? ...
>
>|||Hi Cindy,
Thanks for the information. I don't know if I have any memo fields -
how can I tell?
Would the column just show up as blank in Excel?
Thanks!
Cindy Winegarden wrote:
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
> > Update - I just discovered the *easiest* way to do this! You can open
> > DBF files with Microsoft Excel - then just save as an xls - then import
> > - viola - works!
> >> > I am wondering if anyone knows of a reliable tool for importing Foxpro
> >> > (dbase) (dbf) files to SQL Server? ...|||Hi Cindy,
Yes, I see what you mean - the memo fields are blank in Excel.
I went to the link you listed below, but I don't know which file to
download? They all seem like service packs. Is this the right page?
Help.
THANKS
Cindy Winegarden wrote:
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
> > Update - I just discovered the *easiest* way to do this! You can open
> > DBF files with Microsoft Excel - then just save as an xls - then import
> > - viola - works!
> >> > I am wondering if anyone knows of a reliable tool for importing Foxpro
> >> > (dbase) (dbf) files to SQL Server? ...|||"JC HARRIS" <harris1113@.fake.com> wrote in message
news:%230YkfWm3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> What Cindy says is correct. I could not use the excel method because of
the
> dbf size (overflows the excel program). I followed Cindy's instrcution on
> another newsgroup and it worked great.
If you have it available, you might the Office 2007 Beta version of Excel.
It increases the number of records immensely.
Jonathan|||Hi!
http://msdn.microsoft.com/vfoxpro/downloads/updates/ , second item. It
points to
http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en .
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158946224.807292.64180@.m7g2000cwm.googlegroups.com...
> Hi Cindy,
> Yes, I see what you mean - the memo fields are blank in Excel.
> I went to the link you listed below, but I don't know which file to
> download? They all seem like service packs. Is this the right page?
> Help.
> THANKS
>
> Cindy Winegarden wrote:
>> Hi!
>> Yes, older format DBFs can be opened with Excel but you will lose the
>> content of any Memo fields. As an alternative you can download and
>> install
>> the FoxPro and Visual FoxPro OLE DB data provider from
>> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
>> Import Wizard or set up a linked server.
>> --
>> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
>> cindy@.cindywinegarden.com
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
>> > Update - I just discovered the *easiest* way to do this! You can open
>> > DBF files with Microsoft Excel - then just save as an xls - then import
>> > - viola - works!
>> >> > I am wondering if anyone knows of a reliable tool for importing
>> >> > Foxpro
>> >> > (dbase) (dbf) files to SQL Server? ...
>|||*** Sent via Developersdex http://www.developersdex.com ***

IMPORT DBASE IV (DBF) FILE TO SQL SERVER 2005

Hi,
I am wondering if anyone knows of a reliable tool for importing Foxpro
(dbase) (dbf) files to SQL Server?
I have inherted this task from a former employee. I have been using an
Access database with an ODBC link to SQL Server for the import task -
and it worked fine until last week. For some reason, it just quit
importing one of the dbase files.
So, I tried use "db workbench" to convert the dbase file to a text file
first, then tried to import it into Access. It apparently "worked", but
the data got totally corrupted in the process.
Now, I am back at square one - and I need a tool I can use to either
1.) import the dbase file directly to SQL Server, or 2.) a reliable
tool for converting the dbase file to a text file, csv, or xls file for
importing into SQL Server.
Any ideas/suggestions greatly appreciated!
Thanks muchCORRECTION: I used "DBF Viewer", not "db workbench" to convert to text
file
tootsu...@.gmail.com wrote:
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much|||I have the same issue, and found that the driver for dbf files is not
included in the sql2005 install. I had to go to msdn.microsoft.com to get
the foxpro driver files and installed it. I can now at least locate the dbf
and attempt the import, but it is only importing the first 20 rows of 16,000
set. Interested to see what other answers you get becasue I could get only
one person even attempting to help me, and she got me as far as this.
<tootsuite@.gmail.com> wrote in message
news:1158866292.228863.307680@.h48g2000cwc.googlegroups.com...
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much
>|||Update - I just discovered the *easiest* way to do this! You can open
DBF files with Microsoft Excel - then just save as an xls - then import
- viola - works!
Don't know why I didn't discover this earlier. No need for any special
tools, or dts.
JC HARRIS wrote:[vbcol=seagreen]
> I have the same issue, and found that the driver for dbf files is not
> included in the sql2005 install. I had to go to msdn.microsoft.com to get
> the foxpro driver files and installed it. I can now at least locate the db
f
> and attempt the import, but it is only importing the first 20 rows of 16,0
00
> set. Interested to see what other answers you get becasue I could get only
> one person even attempting to help me, and she got me as far as this.
>
> <tootsuite@.gmail.com> wrote in message
> news:1158866292.228863.307680@.h48g2000cwc.googlegroups.com...|||Hi!
Yes, older format DBFs can be opened with Excel but you will lose the
content of any Memo fields. As an alternative you can download and install
the FoxPro and Visual FoxPro OLE DB data provider from
msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
Import Wizard or set up a linked server.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
> Update - I just discovered the *easiest* way to do this! You can open
> DBF files with Microsoft Excel - then just save as an xls - then import
> - viola - works!
[vbcol=seagreen]|||What Cindy says is correct. I could not use the excel method because of the
dbf size (overflows the excel program). I followed Cindy's instrcution on
another newsgroup and it worked great.
"Cindy Winegarden" <cindy@.cindywinegarden.com> wrote in message
news:OwD1qPl3GHA.4924@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
>
>
>|||Hi Cindy,
Thanks for the information. I don't know if I have any memo fields -
how can I tell?
Would the column just show up as blank in Excel?
Thanks!
Cindy Winegarden wrote:[vbcol=seagreen]
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
>|||Hi Cindy,
Yes, I see what you mean - the memo fields are blank in Excel.
I went to the link you listed below, but I don't know which file to
download? They all seem like service packs. Is this the right page?
Help.
THANKS
Cindy Winegarden wrote:[vbcol=seagreen]
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
>|||"JC HARRIS" <harris1113@.fake.com> wrote in message
news:%230YkfWm3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> What Cindy says is correct. I could not use the excel method because of
the
> dbf size (overflows the excel program). I followed Cindy's instrcution on
> another newsgroup and it worked great.
If you have it available, you might the Office 2007 Beta version of Excel.
It increases the number of records immensely.
Jonathan|||Hi!
http://msdn.microsoft.com/vfoxpro/downloads/updates/ , second item. It
points to
http://www.microsoft.com/downloads/...&displaylang=en .
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158946224.807292.64180@.m7g2000cwm.googlegroups.com...
> Hi Cindy,
> Yes, I see what you mean - the memo fields are blank in Excel.
> I went to the link you listed below, but I don't know which file to
> download? They all seem like service packs. Is this the right page?
> Help.
> THANKS
>
> Cindy Winegarden wrote:
>

Import Data from ACT to SQL

I am releasing my CRM application today and I have one more task to accomplish by months end. I need to import all of our data from ACT into SQL. If you know anything about ACT, it uses a flat table in DB2 or DB5. My new application uses SQL Server with normalized tables. How can I import this data and map it properly to my SQL tables? As a side note, all the ACT data has been exported to an Access table, perhaps it is easier to use it rather than the ACT DB. So I have two options available.

Thanks.PD,
Did you have any luck with this? I need to do the same thing.

ScAndal|||been quite awhile since i played with ACT, but i think there is a driver that will read those files. you may have to export out of ACT first. DTS would be your best option. I would read the files into holding tables and then develop some intermediate tables that map up to your SQL tables. then you would just need to write the various selects and updates to force the data into your new model before doing your final import. it's kind of a typical data scrubbing type app. the nice thing is if you can get the DTS package working, you could reimport multiple times if necessary.

cs|||I ended up importing the ACT data to Excel, cleaned it up and then built a DTS pkg to pull it to my tables.

Import Data and Identity fields

When i run the "Import Data" task from SQL 2005 Managment Studio and select the "Copy data from one or more tables or views" option, I notice the task does not re-create the "Identity" field definitions on the destination table. (SQL 2000 EM did this.) Is there a way to tell the import task to include the identity field definition when creating the destiination tables?

The only work around i've found in SQL Mgmt Studio takes multiple steps:

1) run Generate scripts on the source database, and select all tables to create the table scripts (this includes the identity field on each table)

2) run the script on the destination database to created the tables

3) run the import task, edit mappings and select "enable identity insert".

this is really a pain since i could accomplish this all in 1 step in SQL 2000 EM.

Any suggestions? Please help!

Microsoft? You there? Anybody?|||

You might get more traction on this in the SSIS forum, but I'll give it a try. By the way, this is an all-volunteer forum, so if you need immediate support, see http://microsoft.com/support. It's not only us Microsoft working folks in here, we just maintain the forum and try to help as much as we can.

This series of posts might help:

http://www.developerdotstar.com/community/node/727#comment-8456

Basically it involves the "FAST LOAD" option.

Buck Woody

SQL Server Team

Import Data and Identity fields

When i run the "Import Data" task from SQL 2005 Managment Studio and select the "Copy data from one or more tables or views" option, I notice the task does not re-create the "Identity" field definitions on the destination table. (SQL 2000 EM did this.) Is there a way to tell the import task to include the identity field definition when creating the destiination tables?

The only work around i've found in SQL Mgmt Studio takes multiple steps:

1) run Generate scripts on the source database, and select all tables to create the table scripts (this includes the identity field on each table)

2) run the script on the destination database to created the tables

3) run the import task, edit mappings and select "enable identity insert".

this is really a pain since i could accomplish this all in 1 step in SQL 2000 EM.

Any suggestions? Please help!

Microsoft? You there? Anybody?|||

You might get more traction on this in the SSIS forum, but I'll give it a try. By the way, this is an all-volunteer forum, so if you need immediate support, see http://microsoft.com/support. It's not only us Microsoft working folks in here, we just maintain the forum and try to help as much as we can.

This series of posts might help:

http://www.developerdotstar.com/community/node/727#comment-8456

Basically it involves the "FAST LOAD" option.

Buck Woody

SQL Server Team

Import CSV File with FTP as Source

I want to use a FTP Task to obtain a file on a remote server, then
transfer into a table.
I'm sure it can be done, but there aren't many tutorials explaining
how it works only thin acknowledgments.Hi
"JGiotta" wrote:
> I want to use a FTP Task to obtain a file on a remote server, then
> transfer into a table.
> I'm sure it can be done, but there aren't many tutorials explaining
> how it works only thin acknowledgments.
>
You can use DTS for SQL 2000 or SSIS for ASQL 2005 expecially if there are
multiple files. For DTS check out http://www.sqldts.com/302.aspx and
http://www.sqldts.com/246.aspx
You will need to do this in two stages, get the FTP files and then import
them.
John

Import CSV File with FTP as Source

I want to use a FTP Task to obtain a file on a remote server, then
transfer into a table.
I'm sure it can be done, but there aren't many tutorials explaining
how it works only thin acknowledgments.Hi
"JGiotta" wrote:

> I want to use a FTP Task to obtain a file on a remote server, then
> transfer into a table.
> I'm sure it can be done, but there aren't many tutorials explaining
> how it works only thin acknowledgments.
>
You can use DTS for SQL 2000 or SSIS for ASQL 2005 expecially if there are
multiple files. For DTS check out http://www.sqldts.com/302.aspx and
http://www.sqldts.com/246.aspx
You will need to do this in two stages, get the FTP files and then import
them.
John

Import CSV File with FTP as Source

I want to use a FTP Task to obtain a file on a remote server, then
transfer into a table.
I'm sure it can be done, but there aren't many tutorials explaining
how it works only thin acknowledgments.
Hi
"JGiotta" wrote:

> I want to use a FTP Task to obtain a file on a remote server, then
> transfer into a table.
> I'm sure it can be done, but there aren't many tutorials explaining
> how it works only thin acknowledgments.
>
You can use DTS for SQL 2000 or SSIS for ASQL 2005 expecially if there are
multiple files. For DTS check out http://www.sqldts.com/302.aspx and
http://www.sqldts.com/246.aspx
You will need to do this in two stages, get the FTP files and then import
them.
John

Import and Overwrite DB from Text File

First off, I am not a DBA, not even remotely close. Anywho, I have been given the task of figuring out how to import from a comma delimited text file into 2 columns of an existing database. The task is as follows:

- A daily text file is created by a Unix DB and placed on a folder local to the SQL Server.
- I am to take this file and import into an existing MS SQL2005 DB that has 3 columns.
- AccountID, AccountName, DateRecordCreated
- The imported data has to overwrite all existing SQL DB data.
- This is to run automated on a daily schedule.

Being a SysAdmin, this sounds super simple to do but I have wasted 2 full days in trying to figure out how to make this happen using SSIS. All I want to know is if I am in the right track in focusing on SSIS for a solution. Any additional How To's would be greatly appreciated. BTW, the text file looks something like this...

AccountID,AccountName
A123456,Joe Smith, M.D.
A234567,John H. Dude,M.D.

Thanx much
Recipe as follows:

Control flow:
1 - Execute SQL Task to issue a SQL statement: "truncate table account_table"
1 - Data flow to load the CSV file to the data base

Data flow:
1 - Flat File Source
1 - OLE DB Destination set to use "Table or view - fast load"

Then setup a SQL Server Agent job to execute the SSIS package.

import .sql script into sql server

Hi,

Maybe this is an easy task, but I'm having a really hard time figuring
out how to do this. I'm a complete newbie to SQL Server.

I have a database dump file from MySQL that's in .sql format. I'm
trying to figure out how to import that into SQL Server 2000 so that
I'll be able to manipulate it in a gui format, rather than command
line. I can't find any import that takes a .sql file. I've been
trying to load it into the query analyzer and am also having problems
with that. Initially I had problems because there were question marks
within some of my data. I've removed those but when I run the query I
still get a bunch of syntax errors. Looking at the errors, the syntax
seems correct.

Does anyone know of a way to import a .sql script without having to
import it into the query analyzer? It seems like it should be a
no-brainer.... but perhaps my brain is lacking... I don't know!

Thanks for the help!

--jet

the following are the errors that I get and the associated code:
------------------------
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near ','.

CODE: INSERT INTO answers VALUES
('travelMethodRadio','jackwichita@.montana.com','te lemark'),

The second line is line 17, and it is followed by more insert values.
------------------------
Server: Msg 170, Level 15, State 1, Line 7093
Line 7093: Incorrect syntax near 'a'.

CODE: ('groupSlopeTravelText','kathryn232@.jhmg.com','Thi s is
completely dependent on conditions and if it is a heavily used area or
a fairly pristine area'),

This is just one in a long list of insert values... no idea why there
is a problem with this particular one.
------------------------"jet" <jessey_tase@.yahoo.com> wrote:
> Hi,
> Maybe this is an easy task, but I'm having a really hard time figuring
> out how to do this. I'm a complete newbie to SQL Server.
> I have a database dump file from MySQL that's in .sql format. I'm
> trying to figure out how to import that into SQL Server 2000 so that
> I'll be able to manipulate it in a gui format, rather than command
> line. I can't find any import that takes a .sql file. I've been
> trying to load it into the query analyzer and am also having problems
> with that. Initially I had problems because there were question marks
> within some of my data. I've removed those but when I run the query I
> still get a bunch of syntax errors. Looking at the errors, the syntax
> seems correct.
> Does anyone know of a way to import a .sql script without having to
> import it into the query analyzer? It seems like it should be a
> no-brainer.... but perhaps my brain is lacking... I don't know!
> Thanks for the help!
> --jet
>
> the following are the errors that I get and the associated code:
> -----------------------
--
> Server: Msg 170, Level 15, State 1, Line 17
> Line 17: Incorrect syntax near ','.
> CODE: INSERT INTO answers VALUES
> ('travelMethodRadio','jackwichita@.montana.com','te lemark'),
> The second line is line 17, and it is followed by more insert values.
> -----------------------
--
> Server: Msg 170, Level 15, State 1, Line 7093
> Line 7093: Incorrect syntax near 'a'.
> CODE: ('groupSlopeTravelText','kathryn232@.jhmg.com','Thi s is
> completely dependent on conditions and if it is a heavily used area or
> a fairly pristine area'),
> This is just one in a long list of insert values... no idea why there
> is a problem with this particular one.
> -----------------------
--

jet,

Before answering your question... if you use the ODBC driver for MySQL, you
can use SQL Server DTS to get table structure and data to/from MySQL. IMHO,
it's much easier that way. However, to answer your question...

The problem you're having would be the same going from SQL Server back to
MySQL: they both use extensions to SQL. Query Analyzer is definitely the
tool to use if you want to run a MySQL .sql file vs your SQL Server.
However, some things to keep in mind..

- Neither SQL Server nor Query Analyzer recognize "#" as a comment
character: use "--" instead.

- MySQL escapes single quotes in a string literal with a backslash (\'): SQL
Server escapes them with an extra single quote ('').

- MySQL allows you to "chain" records for an insert (this is the error
you're getting above). When I generate a SQL file from MySQL I use
phpMyAdmin and if I de-select the option "Extended Inserts" it generates an
insert per record instead of a single insert (what you want for SQL Server).
Basically, you're getting the error because you're trying

INSERT SomeTable VALUES ('a1', 'a2', a3'), ('b1', 'b2', b3')

when you want

INSERT SomeTable VALUES ('b1', 'b2', b3')
INSERT SomeTable VALUES ('a1', 'a2', a3')

Hope this helps!

Craig|||Jet,
If you are a newbie to MS SQL Server 2000 and want to get up to speed
really quickly, our videos give you expert instruction on what you really
need to know about SQL Server. It's like reading a 1200 page book in a few
hours.
To answer your question below, a great way to do this is by using a DTS
package. It's easy to write, makes importing as simple as Excel, and can be
re-used.

For full info and examples of using a DTS package, download our videos at
www.technicalVideos.net
Best regards,
Chuck Conover
www.TechnicalVideos.net

"jet" <jessey_tase@.yahoo.com> wrote in message
news:c3fc98c2.0401251736.2fb4db30@.posting.google.c om...
> Hi,
> Maybe this is an easy task, but I'm having a really hard time figuring
> out how to do this. I'm a complete newbie to SQL Server.
> I have a database dump file from MySQL that's in .sql format. I'm
> trying to figure out how to import that into SQL Server 2000 so that
> I'll be able to manipulate it in a gui format, rather than command
> line. I can't find any import that takes a .sql file. I've been
> trying to load it into the query analyzer and am also having problems
> with that. Initially I had problems because there were question marks
> within some of my data. I've removed those but when I run the query I
> still get a bunch of syntax errors. Looking at the errors, the syntax
> seems correct.
> Does anyone know of a way to import a .sql script without having to
> import it into the query analyzer? It seems like it should be a
> no-brainer.... but perhaps my brain is lacking... I don't know!
> Thanks for the help!
> --jet
>
> the following are the errors that I get and the associated code:
> -----------------------
--
> Server: Msg 170, Level 15, State 1, Line 17
> Line 17: Incorrect syntax near ','.
> CODE: INSERT INTO answers VALUES
> ('travelMethodRadio','jackwichita@.montana.com','te lemark'),
> The second line is line 17, and it is followed by more insert values.
> -----------------------
--
> Server: Msg 170, Level 15, State 1, Line 7093
> Line 7093: Incorrect syntax near 'a'.
> CODE: ('groupSlopeTravelText','kathryn232@.jhmg.com','Thi s is
> completely dependent on conditions and if it is a heavily used area or
> a fairly pristine area'),
> This is just one in a long list of insert values... no idea why there
> is a problem with this particular one.
> -----------------------
--