importing a text file into sql database in vb

radhakrishan1
radhakrishan1 used Ask the Experts™
on
Can anybody pls help me urgently in giving me the code for importing a text file into a sql database in vb.

Thanks a lot.

Sample of text file :
20112002,1120,08039,031,000001,596,555855,555855,L,0223,20112002,1216,N,0
20112002,1120,08039,031,000006,568,555872,555877,L,0223,20112002,1216,N,0

Can u pls give me complete code for this.
Text file contains 14 fields each sepearted with comma.
Regards

Lokesh
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Project manager
Commented:
Is every "," a delimter for a column ???

Dim FileImport as integer
' Open file
FileImport = FreeFile
Open (filename) For Input As FileImport
Do Until EOF(FileImport)
   Line Input #FileImport, Inputstring
   field1 = Left(inputstring, InStr(inputstring, ",") - 1)
   inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
   field2 = Left(inputstring, InStr(inputstring, ",") - 1)
   inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
   ...
   field14 = Left(inputstring, InStr(inputstring, ",") - 1)
   inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))

' Now you can do a insert ...
   sqlstatement = "insert into ??? values "("  & field1 & ",'" & field2 & "'," & field3 & ... & ")"

loop


Remark: supposed field2 must be inserted into a textfield in database --> don't forget '

Author

Commented:
I would be very happy if u could pls give me the complete code for this.

As u asked text file contains 14 fields sepearted by columns and all of them contains numeric data except 2 fields. So do I need to do formatting(means converting them to numeric) for them before inserting into the database.
"," is a delimeter for every column.
Dirk HaestProject manager

Commented:
Dim FileImport as integer
' Open file
FileImport = FreeFile
Open (filename) For Input As FileImport
Do Until EOF(FileImport)
  Line Input #FileImport, Inputstring
  field1 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field2 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field3 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field4 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field5 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field6 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field7 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field8 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field9 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field10 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field11 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field12 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field13 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))
  field14 = Left(inputstring, InStr(inputstring, ",") - 1)
  inputstring= Right(inputstring, Len(inputstring) - InStr(inputstring, ","))

' Now you can do a insert ...
  sqlstatement = "insert into ??? values "("  & field1 & "," & field2 & "," & field3 & "," & field4 & "," & field5 & "," & field6 & "," & field7 & "," & field8 & ",'" & field9 & "'," & field10 & "," & field11 & "," & field12 & ",'" & field13 & "'," & field14 & ")"

loop

Author

Commented:
Thanks a ton for ur help, but pls its my humble request, if u could give me complete code.

Means, how i should mention the connection part to the sql database in the program.

Again a request to give complete code in all the aspect.

thanks

lokesh
Dirk HaestProject manager

Commented:
I only made this part of the code...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial