Link to home
Start Free TrialLog in
Avatar of radhakrishan1
radhakrishan1

asked on

importing a text file into sql database in vb

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
ASKER CERTIFIED SOLUTION
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of radhakrishan1
radhakrishan1

ASKER

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.
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image
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
Avatar of radhakrishan1
radhakrishan1

ASKER

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
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image
I only made this part of the code...