how to upload text file to SQL using VB? Urgent

**wad3v4** · 10-23-2008, 04:18 AM

Its been almost 2 weeks that im trying to figure this out and next week is already the deadline. I hope someone can help me with this.
I am developing a system whereby the user will upload a text file via visual studio(using the fileupload control) and the data in the file will be inserted in SQL table. However the txt file are of fixed length therefore i included a schema file in order for the data to be inserted in the right column.
And then i did more and more research and i find that i need to create a stored procedure too and so i did. And the problem now is that my SQL select statement all can work in SQL but i do not know how to put all this data together and do not know any coding whereby i can insert the data from the file into SQL table using VB.

below are the schama.ini the text file and the stored procedure coding. can anyone help??? thnkx!

code.txt
MORNING CONTAINER AIR AV
HALEX FRIENGT AH
ARTACTIC AK
ARTATIC BN
AMARA SUITE HOTEL AC
ARMANY SWEDEN AMBASSY AP
CARGO FRIEGHT HOLS AJ
AREA OF DEFENCE DEF AT
CONTAINER SHIPPER AW
WHITE ROSE AMBASSY AZ
ALLIES DNA SWE AI

schema.ini
[code.TXT]
ColNameHeader = False
CharacterSet = ANSI
Format = FixedLength
Col1=FullName Text width 23
Col2=OpCode Text Width 2

stored procedure
create procedure spUploadFile
@fullname varchar(23),
@opCode char(2)
AS
Insert Operator(FullName, OpCode)
values(@fullname, @opcode)
return

**Hack** · 10-23-2008, 08:17 AM

Welcome to DevX

Does each line go into a single field, or are the lines supposed to be split up and go in individual fields?

**jamesbandido** · 10-23-2008, 10:17 AM

sorry for asking but why use fixedlength when it would be better to use delimiters for your data ... it would be easier to read too ...

"MORNING CONTAINER AIR","AV"
"HALEX FRIENGT","AH"
"ARTACTIC","AK"
"ARTATIC","BN"
"AMARA SUITE HOTEL","AC"
"ARMANY SWEDEN AMBASSY","AP"
"CARGO FRIEGHT HOLS","AJ"
"AREA OF DEFENCE DEF","AT"
"CONTAINER SHIPPER","AW"
"WHITE ROSE AMBASSY","AZ"
"ALLIES DNA SWE","AI"

**wad3v4** · 10-23-2008, 09:53 PM

Hi Hack

hmmm I dun exactly get what u mean by that but here's wad i need

MORNING CONTAINER AIR AV
HALEX FRIENGT AH
ARTACTIC AK
ARTATIC BN
AMARA SUITE HOTEL AC
ARMANY SWEDEN AMBASSY AP
CARGO FRIEGHT HOLS AJ
AREA OF DEFENCE DEF AT
CONTAINER SHIPPER AW
WHITE ROSE AMBASSY AZ
ALLIES DNA SWE AI

the information above should be like classified into 2 columns with header FullName and OpCode. So that means that there will be abundant of rows depending on the content of the text file, I might even have a few thousands more rows where that come from.

Hi Jamesbandido

It's not that i don't want to but the following txt file is a raw data from my company and there are many other files with a lot more contents where this comes from. The company ask me to write a system where this file can be sorted out but with my minimal knowledge on IT i'm taking forever to finish up this task.

**Hack** · 10-24-2008, 08:17 AM

Originally Posted by wad3v4

hmmm I dun exactly get what u mean by that

You are using a database. You do know what a field is, right?

**wad3v4** · 10-25-2008, 05:25 AM

hmmm... how should i put this. this system tad i am suppose to create should function in such a way that the end user upload a fixed length text file using VB then the content of the file will be inserted into a table tad is already created in SQL. The end user will only be interacting with VB. The fixed length text file that the user will be uploading do not contain any header. so counting by columns in notepad, col 1 to 23 would go into the 1st field then col 24 to 26 will go to the second field and so on.
Isnt field something like in my case FullName and OpCode? the data will go in accordingly?
Sorrie for the confusion i guess im not tad good in explaining but i hope i made myself clearer now

. Thanks