asked on

Problems uploading this particular file with ASP into a Database, line by line

I have been provided the template file that a client will be uploading to their Access database to populate the DB.

I am having a problem doing the following

1: Looping past the first line as it is the column name
2: Reading consecutive lines without reading the next line sometimes in error
3: Reading Line by line as ALL data is uploaded at the same time.

Please could someone look into this for me and advise.
PRICELST20130516.txt

ASKER CERTIFIED SOLUTION

Gary

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Destiny Amana

ASKER

GaryC123,

I am having problems trying to get the delimiters in the file.

could you have a look for me as well.

Gary

strLinePart = split(strLine,"|")
should be
strLinePart = split(strLine,",")

You will also need to do a replace on the speech marks.

Gary

I cannot test as I don't have an asp environment or access db.

To replace the "

string= replace(arraypart[i],CHR(34),"")

Open in new window

Destiny Amana

ASKER

this is my code

' Declare local variables
                                                Dim objFSO ' FileSystemObject
                                                Dim TS ' TextStreamObject
                                                Dim strLine ' local variable to store Line
                                                Dim strFileName ' local variable to store fileName

                                                strFileName = txtfilename

                                                ' Instantiate the FileSystemObject
                                                Set objFSO = Server.CreateObject("Scripting.FileSystemObject")

                                          ' use Opentextfile Method to Open the text File
                                                Set TS = objFSO.OpenTextFile(strFileName, ForReading, Create)


                                                'Arr = TS.ReadAll
                                                '
                                                'Arr = Split(Arr," ")
                                                '
                                                'For i = 0 To UBound(Arr)
                                                '      Response.Write "<br>" & Arr(i) & "<br>"
                                           'Next

                                                'Response.End ()
                                                '----------------------------------------------------------------------------

' Open the file

                                                If Not TS.AtEndOfStream Then
TS.Skipline
                                                      Do While Not TS.AtendOfStream

                                                            strLine = TS.ReadLine ' Read one line at a time
                                                                  pl = split (strLine," ") '(tab is the file delimiter)



Response.Write "<p> </p>" & pl (0)
                                                      response.End ()

                                                                  pl_0 = replace (pl(0),CHR(34),"")
                                                       pl_1 = replace (pl(1),CHR(34),"")
                                                                  pl_2 = replace (pl(2),CHR(34),"")
                                                                  pl_3 = replace (pl(3),CHR(34),"")
                                                                  pl_4 = replace (pl(4),CHR(34),"")
                                                                  pl_5 = replace (pl(5),CHR(34),"")
                                                                  pl_6 = replace (pl(6),CHR(34),"")
                                                                  pl_7 = replace (pl(7),CHR(34),"")
                                                                  pl_8 = replace (pl(8),CHR(34),"")

                                                                  'Check the number to make sure it is numeric
                                                                  'if isnumeric(web2sms(3)) then
                                                                  'the value is true and so process the number

Gary

Tab is not your delimiter judging by your attachment - it should be a comma

Also I would do the replace on the whole line not each part of the line

strLine = TS.ReadLine ' Read one line at a time
strLine = replace (strLine ,CHR(34),"")

pl = split (strLine," ") '(tab is the file delimiter)

If you want to split by tab you would do

pl = split (strLine,"\t")
(not 100% sure of this in ASP)

Edit
It could be one of these
pl = split (strLine,chr(9))
pl = split (strLine,vbTab)

Scott Fell

I never tried this but seems so easy to use a csv file as a db http://www.connectionstrings.com/microsoft-text-odbc-driver/

Destiny Amana

ASKER

For some reason, even in excel, This file always opens up to 1 cell and does not use the delimiters .

Can anyone help me with this , maybe I need to format prgrammatically the file before breaking it up and uploading it.

Any one?

SOLUTION

Scott Fell

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Destiny Amana

ASKER

The client has now admited that the file we were being provided with was formated wrongly.

Eith that in mind, the help from above has helped a lot.
GAINLOSE20130715