Import Text file to SQL using VB6 1

barbola · Jun 19, 2006

I am replacing an old Perl program that splits up a text file and imports it into a couple of tables in a SQL database, and also has to perform some calculations for other fields in the table.

The text file has one line per detail record, and is always the same, so this should be easy right?

I'm not quite sure where to start on this project. I have a similar application I developed (sort of) that imports an excel sheet, but I had alot of help on Tek Tips with this one as well, but it's just ... different...

The tables are basically Master and Detail. There could be a couple hundred detail records and then I need to also create a master record for the detail based on some grouping options. I know how to do the SQL stuff, but the VB code gets me every time.

thanks.

gmmastros · Jun 19, 2006

I suggest that you push most of the work to SQL Server. SQL Server has a Bulk Insert command that works really well with ASCII files. Do a little research on this, play with query analyzer a little, and then come back with followup questions (if there are any).

http://msdn2.microsoft.com/en-us/library/ms188365.aspx

I recommend you bulk insert the data in to 'staging' tables. These would be temp tables that you create. Once you have the data in to staging tables, you can perform calculations and insert in to real tables.

Let us know how you make out.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

barbola · Jun 19, 2006

Thank for the link. I do appreciate your help George, but I looked at it and Oh My GAWD. There's no way in I am gonna figure that out.

I mean sometimes I understand this type of documentation, a little, but this one looks very complicated (for me) and I don't have time to research anything. I have to do this today. I have no idea what part of that I need or don't need, and can't see any references to ascii files, and no examples, and too many [.] || and all those characters I have no idea what they mean. I have a headache just looking at it.

Can you tell me what part of that I need to use to do specifically what I need to do? IE. Take a text file and put it into a SQL table, I think I might just have to get the Perl program working again, at least temporarily.

thanks

gmmastros · Jun 19, 2006

It's really not as bas as it seems. The examples at the bottom are very helpful. Anyway, let's do a little example: First, the text file:

Code:

245,Apple
246,Orange
247,Grapes
248,Banana
252,Watermelon
253,Cantelope
255,Strawberry
256,Peach

Copy/paste this to notepad, and then save the file.

Then, fire up query analyzer, and copy paste this text.

Code:

Create Table #Temp(Column1 Integer, Column2 VarChar(100))

Bulk  Insert 
#Temp From '[!]C:\TekTips\TestFile.txt[/!]'
With  (
      FieldTerminator=',',
      RowTerminator='\n'
      )

Select * From #Temp

Drop Table #Temp

Usually, you only need to 'mess around' with the file name (in red), the FieldTerminator, and the RowTerminator. I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

Sheco · Jun 19, 2006

You can also do this using SQL Server's DTS, it wont be as fast but you can use vb6-style syntax in the ActiveX steps.

barbola · Jun 19, 2006

I can't use DTS because other users will be running a VB application that does alot more than just this.

George, I created the table and tried to run SQL statement and am getting this error:

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 1 (id).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2, column 1 (id).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 3, column 1 (id).
Server: Msg 4863, Level 16, State 1, Line 1

etc and it stops at ten errors.

I noticed my data doesn't have commas. I set up my fields in the table with the lengths of the data I want to import. IE. the first 40 characters in each row is field1, the next 8 are field2. I set them all up as varchar for now with those lengths.

Same error when I added rowterminator ='\n'

Here is what my data looks like:

ABCCO LTD. W999001 20060609 .0 23428999 111.2 .0 .0 .0 55.5 66.6 77.7 00TEST 199.900 999 5555

That is one record.

gmmastros · Jun 19, 2006

Barb,

When using Fixed Width files, it is best to use a format file. Here's how:

Copy/paste this to notepad and then save to "formatfile.txt"

Code:

8.0
2
1 SQLCHAR 0 40  ""     1  Field1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 8   "\r\n" 2  Field2      SQL_Latin1_General_CP1_CI_AS

The second line in the file (the 2) represents the number of fields. This number should match the number of lines in the format file that follows. Notice where the 40 and the 8 appear. This tells SQL Server how big the fields are.

Now, to use a format file:

Code:

Create Table #Temp(Field1 VarChar(40), Field2 VarChar(8))

Bulk Insert #Temp From 'C:\TekTips\DataFile.txt'
With (
		FormatFile='C:\TekTips\FormatFile.txt',
		RowTerminator = '\r\n'
	)

Select * From #Temp

Drop Table #Temp

If you run this, you will get an error because you haven't accomodated the full size for each row in the text file. After adding the remaining fields (to the format file) and the #Temp table, you should be in good shape. Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

barbola · Jun 19, 2006

I was just looking into it some more and yeah I realized a format file would help, but I wasn't sure how to make one.

Thanks for the info!

B.

gmmastros · Jun 19, 2006

This may be a pain to set up, but I think you will be amazed at the performance. In my experience, nothing beats it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

barbola · Jun 19, 2006

It wasn't alot of work to create the format file, but I am getting an error.

Server: Msg 4839, Level 16, State 1, Line 11
Cannot perform bulk insert. Invalid collation name for source column 23 in format file 'C:\My Documents\formatfile.txt'.

The collation name is right as far as I can tell.

Do I need the \r\n for each row 2 thru 23 or just row 23?

thanks.

barbola · Jun 19, 2006

I took out the \r\n except for the last row. If I remove the collation from the last line, then I get this error:

Code:

Server: Msg 4829, Level 16, State 1, Line 11
Could not bulk insert. Error reading destination table column name for source column 23 in format file 'C:\My Documents\FormatFile.txt'.

Here is my formatfile:

Code:

1 SQLCHAR 0 39  ""     1  Company SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 8   "" 2  Vendor      SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12   "" 3  Date      SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 7   "" 4  IdNum      SQL_Latin1_General_CP1_CI_AS

...
...

23 SQLCHAR 0 4   "\r\n" 23  Contract      SQL_Latin1_General_CP1_CI_AS

Any ideas yet? thanks.

gmmastros · Jun 19, 2006

Do you see that empty string on the first row? That means there is no delimiter (between the fields). The delimiter \r\n only exists for the 23rd (read that as last) record to indicate that the carriage return/line feed end that field.

This part SQL_Latin1_General_CP1_CI_AS is the collation. It specifies how strings are compared, try seeting them to empty string (so that it uses the database's default collation).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

barbola · Jun 19, 2006

ummmm...isn't that what I did?

gmmastros · Jun 19, 2006

Sorry. I started writing the reply, then got interruppted, then finished. The timing was weird.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

barbola · Jun 19, 2006

OK...yeah that happens.

I have tried many variations of that last row with no success:

Code:

23 SQLCHAR 0 4   "\r\n" 23  Contract      SQL_Latin1_General_CP1_CI_AS

Code:

23 SQLCHAR 0 4   "\r\n" 23  Contract

Code:

23 SQLCHAR 0 4   "" 23  Contract      SQL_Latin1_General_CP1_CI_AS

Code:

23 SQLCHAR 0 4   "" 23  Contract      SQL_Latin1_General_CP1_CI_AS

I even changed the field length to 9 thinking that may be the problem.

gmmastros · Jun 19, 2006

You didn't try...

23 SQLCHAR 0 4 "\r\n" 23 ""

Using an empty string for the collation should cause SQL Server to use it's default collation.

You can see what the default collation for the server is with:

Select ServerProperty('collation')

If this doesn't solve the problem, then I suggest you post the entire format file. Since I have 1 row of your data, I'll be able to test it, to see if there is any other weirdness going on.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

barbola · Jun 19, 2006

using "" for collation did not work either, and the default is the collation shown.

Here is the format file and a row of data:

Code:

8.0
23
1 SQLCHAR 0 39	""	1  Company	SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 8	""	2  Vendor	SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12	""	3  KillDate	SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 7	""	4  AvePurWt	SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 6	""	5  [ID]	SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 5	""	6  Tattoo	SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 12	""	7  CarWt	SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 4	""	8  DemCode1	SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 9	""	9  TrimWt1	SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 4	""	10  DemCode2	SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 9	""	11  TrimWt2	SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 4	""	12  DemCode3	SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 9	""	13  TrimWt3	SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 5	""	14  Tattoo2	SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 6	""	15  HogType	SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 7	""	16  Fatmm	SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 7	""	17  Leanmm	SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 7	""	18  YieldClass	SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 10	""	19  Condemned	SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 6	""	20  LotNumber	SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 8	""	21  BasePrice	SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 7	""	22  MIndex	SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 9	"\r\n"	23  Contract	""

Code:

Drop Table #Temp

Create Table #Temp(Company VarChar(39),Vendor Char(8),KillDate VarChar(12),AvePurWt VarChar(7),
		[ID] VarChar(6),Tattoo VarChar(5),CarWt VarChar(12),DemeritCode1 VarChar(4),
		Trimwt1 VarChar(9),DemeritCode2 VarChar(4),Trimwt2 VarChar(9),DemeritCode3 VarChar(4),
		Trimwt3 VarChar(9),Tattoo2 VarChar(5),HogType VarChar(6),Fatmm VarChar(7),
		Leanmm VarChar(7),YieldClass VarChar(7),Condemned VarChar(10),LotNumber VarChar(6),
		BasePrice VarChar(8),MLIndex VarChar(7),mlContract VarChar(9))

Bulk Insert #Temp From 'C:\Data\ABCCO_June06.txt'
With (
        FormatFile='c:\My Documents\FormatFile.txt',
        RowTerminator = '\r\n'
    )

Select * From #Temp

Drop Table #Temp

Data:

ABCCO LTD. W999001 20060609 .0 234289998 199.2 .0 .0 .0 11.1 99.0 99.9 00TEST 999.900 999 5843
ABCCO LTD. W999001 20060609 .0 234589998 100.0 .0 .0 .0 22.2 99.0 99.9 00TEST 999.900 888 5843
ABCCO LTD. W999001 20060609 .0 234689998 99.9130 8.8 .0 .0 11.1 88.8 88.8 00TEST 999.900 111 5844

Before you spend alot of time, I noticed the possibility that the supplier of our data may have inserted a blank space somewhere. The last field is supposed to begin at 195 and it appears to be at 194 but I could be mistaken. I can check that.

Would that cause a problem?

thanks,
Barb.

gmmastros · Jun 19, 2006

Good news.... Bad News.....

I copy/pasted to a notepad doc. I also modified the file names (to match the ones I created).

The good news is that it works for me.

The bad news is that it doesn't solve your problem. I suspect there is a data issue going on, but I can't be sure. Sorry.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

barbola · Jun 19, 2006

The only things I can think of are:

-an issue with the table name #Temp
-an issue with the server being different than the file server (I had to put fileformat.txt on the sql server itself, but the data text file is on the main network).

I can only access the SQL server using PC Anywhere. I will try copying the data to SQL and see if that makes a difference, although the error didnt' say anything about my file.

barbola · Jun 19, 2006

hmmm no, and no, those weren't the problem. I copied the files to the SQL server and ran the query, also changed the table name and no go.

Thank you for your help, I'm glad it works for you LOL

I'll play with it tomorrow, maybe do some more searching. So much for getting it done today... oh well, nobody called me about it.

barb

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Import Text file to SQL using VB6 1

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor