Importing Text File to SQL

HI Guys,

I am doing the following to read the data in a text file and inserting it into SQL.

1) Open db connection
2) Open Text File
3) loop through text file all along inserting each row into the db
4) close the text file
5) close the db connection

However, the text file has over 400 rows/lines of data that need to be inserted into the db. Each line in the text file is a row in the db. At anyrate, the above script times out. Is there a better, faster way to do this? I can't use Bulk Insert due to permissions previlages.

Thanks in Advance!DTS would do it. If you can get a DTS pakage set up and a procedure to run it, you can do it by uploading the file to a place the database can see it, and then run the proc that runs the DTS job.

400 rows isn't much. I do a similar thing with up to 100,000 rows. Not an ideal thing, but it was right for the situation. I had to set the timeouts longer, which is what you can do also.

You need to set a longer timeout in three places.

1) server.scripttimeout
2) connection timeout
3) command timeout

Google for examples. 400 is not a lot, so it's not a bad way to do it. If you don't expect that to grow much, just set the timeouts longer and be done with it.|||Hi, I'm new in the programming. I am developing an application that uses text file as input for the data. Therefore, i need to import the text file to SQL server before I could use it. Do you mind if you could share the sample of your code for importing the text file and convert it to sql.

Importing TEXT File in DTS

Hello Guys,
I Hava a Source text connection and I'd like to take just the first row ( the header, of course) of the file to one table. How can I get this??
Tis is quite Urgent.
Thanxs;Does it have to be DTS?

Why not DTS in to a single column table (varchar(8000)) and the parse out the data in to the final table?|||Because the text files can be larger than 200MB. :(
I need to take just the first Row of the text file to know some important informations.|||You sure you're talking about row size?

That's a long row....|||No. Im speaking about the File.

Look an example of the beggining of the file:
I want to get the first row of the file and put it into a column. Note, just the first row. You can see that the another lines are in a different layout and would make my table very big.

A221539 DPVAT - COD BAR 151BANCO NOSSA
G00000000000000 20040123200401298664000000093373362
G00000000000000 20040123200401298663000000093383362
G00000000000000 20040123200401298669000000051623362
G00000000000000 20040123200401298669000000093383362
G00000000000000 20040123200401298664000000093383362
G00000000000000 20040123200401298661000000093383362
G00000000000000 20040123200401298661000000055433362|||Have you looked at BULK INSERT in BOL?

You can specify first row and last row (ie 1 and 1)

Importing Excel

Hi,

I know this issue exisits in DTS but needs to check still is in SSIS, Also you guys may have a better solution for it.

Issue: When I try to import a column from excel which has data like A,B,C,D,E,4,5 in the destination table has the data type as varchar it imports only A,B,C,D,E and 4 & 5 as nulls. How to fix this.

Set the Excel connection Extended Property, IMEX=1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1294377&SiteID=1|||Thanks, it worked. Appreciated.|||

Imex=1 worked for all the cells except few, this column has data like below

07AWA38V36 0717062042 715018020 07BCB01R17

even in this column some has been imported correctly and few are imported like 7.15E+...

Note: My desination column data type in Varchar.

|||Make sure that Excel doesn't have any formatting for the problem cells or the problem column.|||In the above example for the second row the data was formatted as text becasue it has the leading Zero where as the third is not formatted becuase it doesnt have any leading zero. the issue was in the third row coverting as 7.15E+.. while running the SSIS|||

yes, i′ve got just the same problem.

incredibly it didn′t happen the first time i runned the dts, but now..

|||

..and i just got it..

I selected the whole worksheek, converted the cells into numeric type (format/cells/numeric) and saved. Then i converted the cells into General type again, saved and runned the dts. And it works now.

Better not to use Text types when it happens. When you′ve got a Text type it doesnt work properly, and if you change to General => it doesnt work either. But if you had Numeric types and change to General then it works.

|||

When I change this to Numeric then I will loose the leading zero in the 2 & 3 row.

|||

? no, you wont..

It′s just an excel fail, i doesnt catch properly that there′s a number, not a text, when you had a text previously

Importing Excel

Hi,

I know this issue exisits in DTS but needs to check still is in SSIS, Also you guys may have a better solution for it.

Issue: When I try to import a column from excel which has data like A,B,C,D,E,4,5 in the destination table has the data type as varchar it imports only A,B,C,D,E and 4 & 5 as nulls. How to fix this.

Set the Excel connection Extended Property, IMEX=1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1294377&SiteID=1|||Thanks, it worked. Appreciated.|||

Imex=1 worked for all the cells except few, this column has data like below

07AWA38V36 0717062042 715018020 07BCB01R17

even in this column some has been imported correctly and few are imported like 7.15E+...

Note: My desination column data type in Varchar.

|||Make sure that Excel doesn't have any formatting for the problem cells or the problem column.|||In the above example for the second row the data was formatted as text becasue it has the leading Zero where as the third is not formatted becuase it doesnt have any leading zero. the issue was in the third row coverting as 7.15E+.. while running the SSIS|||

yes, i′ve got just the same problem.

incredibly it didn′t happen the first time i runned the dts, but now..

|||

..and i just got it..

I selected the whole worksheek, converted the cells into numeric type (format/cells/numeric) and saved. Then i converted the cells into General type again, saved and runned the dts. And it works now.

Better not to use Text types when it happens. When you′ve got a Text type it doesnt work properly, and if you change to General => it doesnt work either. But if you had Numeric types and change to General then it works.

|||

When I change this to Numeric then I will loose the leading zero in the 2 & 3 row.

|||

? no, you wont..

It′s just an excel fail, i doesnt catch properly that there′s a number, not a text, when you had a text previously

Importing dBase files with the SSIS Import/Export Wizard

I saw this post by dterrie in the Wishlist thread and I just wanted to second it:

"How about bringing back a simple dBase import. The SSIS guys are clearly FAR out of touch with reality if they think people who handle data no longer need to work with dbf files. I've seen alot of dumb stuff in my day, bit this is just sheer brilliance. I just love the advice of first importing into Access and then importing the Access table. Gee, why didn't I think of such a convenient solution. I could have had a V-8."

I've been struggling with this the last couple days and finally decided to import the dBase III file into Access and then import that into SQL Server 2005. Imagine my surprise when I discovered this was the current recommended method.

That's just ridiculous. Can someone tell me why they would reduce some of the functionality of SQL Server from 2000 to 2005? This was a very easy process in SQL Server 2000...

Philip,

Could you record your request here:

http://lab.msdn.microsoft.com/productfeedback/default.aspx

That way a request will be passed directly to our bug system. It will increase a chance to address it sooner, and you will be informed about the progress.

Thanks.

|||

Thanks Bob! That's a great idea.

I know you guys have been catching some flack over the anemic ODBC support.

Here's hoping there is a service pack for it soon!

Importing data from MS Access using DTS 2005

Guys,

I am new to DTS 2005; having trouble on how to connect to MS Access to pull data? what kind of connection manager should I use (OLE?) and what specific Data Flow Source type? Please respond.

Thanks

You will need to set your connection manager to use OLE DB and the Microsoft Jet 4.0 OLE Provider. This will connect you to any version of Access above version 4 from memory.

Once you have added the dataflow task, under the dataflow tab add you OLD db source, and then your destination. If you do need to do any transforms you would need to add them from the tool box under the data flow tab.

|||

Glenn,

Thanks; still having some issues; am trying to connect from access and dump to an excel spreadsheet.

I used the OLE DB to connect; and in my data flow task, I created a connection manager for Excel (specifing file path where the spreadsheet was located). I then identified an Excel Spreadsheet as my data flow destination point. When I executed, I got the following errors:

Error: 0xC0202009 at Pull From Access, Excel Destination [357]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0202025 at Pull From Access, Excel Destination [357]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Pull From Access, DTS.Pipeline: component "Excel Destination" (357) failed the pre-execute phase and returned error code 0xC0202025.

Anything I am doing wrong?

|||

Can anyone throw some light on this issue. Im facing almost a similar problem. I have an OLEDB source which connects to SQL server pulls some records out of a table and i want them to be exported to a Excel File which i have already created. So i added a New connection using the Connection Manager for Excel Files and connected to the already existing destination in which i have defined some column names. When i maped the columns it initially gave me some conversion errors for teh varchar fields, then finally i converted all the varchar fields to "Unicode text stream [DT_NTEXT]", now there were no conversion errors. But when i executed the package i got the following errors:

[Excel Destination [185]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Destination [185]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[DTS.Pipeline] Error: component "Excel Destination" (185) failed the pre-execute phase and returned error code 0xC0202025.

Any help is appreciated. Thanks a lot in advance.

|||

I got most of the things done (I created an Excel File and each time im creating sheets i.e., Creating and dropping tables which deltes the data and gives a fresh sheet to insert the data) but in my package im creating an Excel sheet/TAble using the Execute SQL statement

"CREATE TABLE `CUSTOMER_ORDER_ITEM` (`TransferDate` DateTime,
`ErrCode` LongText,
`ErrDesc` LongText,
`ErrData` LongText,
`ErrorStatus` Short
)
GO
"

before that im dropping the sheet/table using the below Execute SQL statement

"DROP TABLE `CUSTOMER_ORDER_ITEM` "

So it is throwing an error for the first time when the package is running. I need to know whether there is a way to check that the Sheet/Table exists before deleting the Sheet/Table.

Thanks in advance. Any other work around is also appreciated

|||

You can use GetOleDbSchemaTable to check whether the sheet exists.

http://support.microsoft.com/kb/309488

Importing data from MS Access using DTS 2005

Guys,

I am new to DTS 2005; having trouble on how to connect to MS Access to pull data? what kind of connection manager should I use (OLE?) and what specific Data Flow Source type? Please respond.

Thanks

You will need to set your connection manager to use OLE DB and the Microsoft Jet 4.0 OLE Provider. This will connect you to any version of Access above version 4 from memory.

Once you have added the dataflow task, under the dataflow tab add you OLD db source, and then your destination. If you do need to do any transforms you would need to add them from the tool box under the data flow tab.

|||

Glenn,

Thanks; still having some issues; am trying to connect from access and dump to an excel spreadsheet.

I used the OLE DB to connect; and in my data flow task, I created a connection manager for Excel (specifing file path where the spreadsheet was located). I then identified an Excel Spreadsheet as my data flow destination point. When I executed, I got the following errors:

Error: 0xC0202009 at Pull From Access, Excel Destination [357]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0202025 at Pull From Access, Excel Destination [357]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Pull From Access, DTS.Pipeline: component "Excel Destination" (357) failed the pre-execute phase and returned error code 0xC0202025.

Anything I am doing wrong?

|||

Can anyone throw some light on this issue. Im facing almost a similar problem. I have an OLEDB source which connects to SQL server pulls some records out of a table and i want them to be exported to a Excel File which i have already created. So i added a New connection using the Connection Manager for Excel Files and connected to the already existing destination in which i have defined some column names. When i maped the columns it initially gave me some conversion errors for teh varchar fields, then finally i converted all the varchar fields to "Unicode text stream [DT_NTEXT]", now there were no conversion errors. But when i executed the package i got the following errors:

[Excel Destination [185]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Destination [185]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[DTS.Pipeline] Error: component "Excel Destination" (185) failed the pre-execute phase and returned error code 0xC0202025.

Any help is appreciated. Thanks a lot in advance.

|||

I got most of the things done (I created an Excel File and each time im creating sheets i.e., Creating and dropping tables which deltes the data and gives a fresh sheet to insert the data) but in my package im creating an Excel sheet/TAble using the Execute SQL statement

"CREATE TABLE `CUSTOMER_ORDER_ITEM` (`TransferDate` DateTime,
`ErrCode` LongText,
`ErrDesc` LongText,
`ErrData` LongText,
`ErrorStatus` Short
)
GO
"

before that im dropping the sheet/table using the below Execute SQL statement

"DROP TABLE `CUSTOMER_ORDER_ITEM` "

So it is throwing an error for the first time when the package is running. I need to know whether there is a way to check that the Sheet/Table exists before deleting the Sheet/Table.

Thanks in advance. Any other work around is also appreciated

|||

You can use GetOleDbSchemaTable to check whether the sheet exists.

http://support.microsoft.com/kb/309488

Importing data

Hi Guys
Can someone give me some directions on importing data from another server
e.g.
INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
( Suburb, PostCode, State)
SELECT Suburb, PostCode, State
FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
Thanks heaps...BooksOnLine not too helpfulThe easiest way I've seen is to use the Import wizard. In the SQL Server
Management Studio Object Explorer, right-click on the target (or source)
database and select All Tasks-->Import Data (or Import Data).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:3wIRg.14671$b6.160428@.nasal.pacific.net.au...
> Hi Guys
> Can someone give me some directions on importing data from another server
> e.g.
> INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
> ( Suburb, PostCode, State)
> SELECT Suburb, PostCode, State
> FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
> Thanks heaps...BooksOnLine not too helpful
>|||Harry
Have you created a linked server to SQL Server 2000 ?
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:3wIRg.14671$b6.160428@.nasal.pacific.net.au...
> Hi Guys
> Can someone give me some directions on importing data from another server
> e.g.
> INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
> ( Suburb, PostCode, State)
> SELECT Suburb, PostCode, State
> FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
> Thanks heaps...BooksOnLine not too helpful
>

Importing Access table

hi guys,
i have a problem here. i want to import a table from microsoft access, but i encountered an error. it says something like cannot convert into sqlserver format. I notice that the column in the access table that causes the error is actually an auto-number column with long integer. What i have to do to be able to import the table in?
thanxAssuming you want it to continue to be an autonumber/identity it has to be a a numeric field of some sort (so int, big int, numeric, not sure what else is allowed to be an identity).

You are most likely having a problem because the column is set to int and the number in your access db is more then a standard sql int. try changing it to big int and see what happens.|||u mean big int in access or sqlserver? i checked just now, access in the size field only has long integer or replication ID. What if i change it to replication ID? i'm new to sqlserver, i just know access.please help.
thanx|||Are you upsizing or importing ?|||importing...from an access table. coz i am migrating to sqlserver from access, my application can run using access.|||big int in sql server.|||ok then i'll try it later, thanx guys...|||Long integer in access is the same as int in sql. Both are 4 bytes. What are the other field definitions in that table ? Also, what is the definition of the sql server table ?|||you sure on that?? I understood that that used to be the case but it changed with sql server 2k.|||i'm not sure about the bytes of the data type, but in my table there are text data type in my access table. and after convert they will change to nvarchar in sqlserver. i know this because i successfully converted a different table.|||yeah i did not declare anything for the sqlserver table because i use the import wizard and just find the path of my database. then it run into this autonumber problem.|||okie, just had a look through the doco and an int (in sql) should be fine for a long (in access).|||Can you post the mdb with the same schema and a few records ?|||sorry, i can only post it on fri as it's christmas, so i'll let u guys know later ok!
thanx guys and have a merry christmas!