Importing Txt file issue. Need help!

[frustrating]

Hey everyone!

I am trying to set up an access button to go to a website, download its source code and import that txt file into a table so it can be parsed. Nothing too fancy, right?

Well, when I go to import this text file, it imports that data in a weird inconsistent order. The problem is I need the order to read exactly how it is from top to bottom, since the numbers I'm parsing from the code need to correspond to an XL Spreadsheet (which also gets imported).

I have the code set up, and everything is doing what it needs to do perfectly, EXCEPT this import. I know it's something stupid, but I just don't know what!

Right now, I have everything being imported into a text file with an arbitrary delimiter that doesn't appear anywhere in the source code. Is there a way I pull this code in line by line in order into one field with X amount of rows so I can just run queries to pull the numbers I need?

 

[boblarson]

See if this helps

Dim i As Integer
Dim strPathAndFile As String
Dim strHold As String
Dim strSQL As String
 
strPathAndFile = "Your path and file name with extension here"
 
i = FreeFile
 
Open strPathAndFile For Input As #i
 
Do Until EOF(i)
Line Input #i, strHold 
 
strSQL = "Insert INTO TableNameHere ([FieldNameHere]) Values (" Chr(34) & strHold & Chr(34) & ")"
 
CurrentDb.Execute strSQL, dbFailOnError
 
Loop
 
Close #i

 

[frustrating]

Thanks for the reply.

This line is reading as an error, and I cannot figure out why:

strSQL = "Insert INTO TableNameHere ([FieldNameHere]) Values (" Chr(34) & strHold & Chr(34) & ")"

It's getting an expected end statement error.

 

[boblarson]

Sorry forgot one Ampersand (&)

strSQL = "Insert INTO TableNameHere ([FieldNameHere]) Values (" & Chr(34) & strHold & Chr(34) & ")"

 

[frustrating]

Sorry forgot one Ampersand (&)

strSQL = "Insert INTO TableNameHere ([FieldNameHere]) Values (" & Chr(34) & strHold & Chr(34) & ")"

Sorry it took me so long to respond.

I've implemented the changes and I'm getting an error:

Syntax error in string in query expression, and then it gives me the source code in the error box.

 

[frustrating]

I believe the syntax error was due to it not being a single ' since it was bringing in text. However, it's still not looping through the lines of the txt file correctly. It's only inserting the last line.

EDIT: That's not it either. Heh. It appears to run for the first few lines then shoot back a syntax error saying it's missing an operator.

EDIT 2: It's because the text file has apostrophes in it. Any way to circumvent this?

 

[boblarson]

I believe the syntax error was due to it not being a single ' since it was bringing in text.

No, double quotes are quite fine. In fact, changing to single quotes can be problematic as you apparently have single quotes in the text.

However, it's still not looping through the lines of the txt file correctly. It's only inserting the last line.

That sounds like you have the looping not in the right place. Post the exact code you currently have now.

EDIT 2: It's because the text file has apostrophes in it. Any way to circumvent this?

It should be fine using the CHR(34) method I showed. So I want to see what you have as the code which you say you have now that isn't working properly.

 

[frustrating]

No, double quotes are quite fine. In fact, changing to single quotes can be problematic as you apparently have single quotes in the text.

That sounds like you have the looping not in the right place. Post the exact code you currently have now.

It should be fine using the CHR(34) method I showed. So I want to see what you have as the code which you say you have now that isn't working properly.

Thanks for the reply. I made a bunch of edits to that last post, I even confused myself while rereading it. I apologize!

Here is my code right now:

Dim i As Integer
Dim strPathAndFile As String
Dim strHold As String
Dim strSQL As String
strPathAndFile = "P:\test1.txt"
i = FreeFile
Open strPathAndFile For Input As #i
Do Until EOF(i)
Line Input #i, strHold
strSQL = "Insert INTO Master ([Field1]) Values ('" & Chr(34) & strHold & Chr(34) & "')"
CurrentDb.Execute strSQL, dbFailOnError
Loop
Close #i

It's weird, I tried doing the source code for a few sites, Google for one, and it always stops on a line that has a ' in it. I'm most probably doing something wrong, but I can't figure it out!

 

[boblarson]

Well this part is definitely wrong:

Values ('" & Chr(34) & strHold & Chr(34) & "')"

It should be

Values (" & Chr(34) & strHold & Chr(34) & ")"


without the single apostrophes. Chr(34) is a double quote. And you need the text value to be surrounded by double quotes OR single quotes. But if you have single quotes in the value being inserted, it will mess things up if you use single quotes around the value, so double quotes are necessary.

Also, is your field in the table "Master" actually named Field1? If not, it needs to be changed to be the right name. If it is, that is a bad name to use.

 

[frustrating]

Well this part is definitely wrong:

Values ('" & Chr(34) & strHold & Chr(34) & "')"

It should be

Values (" & Chr(34) & strHold & Chr(34) & ")"


without the single apostrophes. Chr(34) is a double quote. And you need the text value to be surrounded by double quotes OR single quotes. But if you have single quotes in the value being inserted, it will mess things up if you use single quotes around the value, so double quotes are necessary.

Understood.
I'm using this now:

Dim i As Integer
Dim strPathAndFile As String
Dim strHold As String
Dim strSQL As String
strPathAndFile = "P:\test1.txt"
i = FreeFile
Open strPathAndFile For Input As #i
Do Until EOF(i)
Line Input #i, strHold
strSQL = "Insert INTO Master ([testfield]) Values (" & Chr(34) & strHold & Chr(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError
Loop
Close #i

Which returns a Run-time error 3075 "Syntax error (missing operator) in query expression ""<DIV id=.

Also, is your field in the table "Master" actually named Field1? If not, it needs to be changed to be the right name. If it is, that is a bad name to use.

They're just test names/ tables for now, but I've noted it.
Again, thanks for taking time to help me.

 

[boblarson]

Looks like you are importing HTML, is that correct? You might need to replace the double quotes in the string first

Values (" & Chr(34) & Replace(strHold, Chr(34), vbNullString) & Chr(34) & ")"

 

[frustrating]

Looks like you are importing HTML, is that correct? You might need to replace the double quotes in the string first

Values (" & Chr(34) & Replace(strHold, Chr(34), vbNullString) & Chr(34) & ")"

SUCCESS! You are a friggin genie wizard person!

 

[nanscombe]

Add the line strHold = Replace(strHold, Chr(39), Chr(39) & Chr(39)) just after your Line Input, this will double up your single quotes.

Line Input #i, strHold
[COLOR="Red"]strHold = Replace(strHold, Chr(39), Chr(39) & Chr(39))[/COLOR]
strSQL = "Insert INTO Master ([testfield]) Values (" & Chr(34) & strHold & Chr(34) & ")"

Or make it HTML compliant by using:
strHold = Replace(strHold, Chr(39), "&apos;")

strHold = Replace(strHold, Chr(34), """)

 

[boblarson]

SUCCESS!

Happy to hear.

You are a friggin genie wizard person!

Well, I wouldn't go that far. Just alot of troubleshooting experience.