Editing an external text file is it possible?

S

smig

Registered User.
Local time
Today, 14:17
Joined
Nov 25, 2009
Messages
2,209
I need to import a text file into my db
If there are any Chr(34) in the file it won't import these lines.
Is it possible to edit the file and replace all Chr(34) into Chr(44) ?
 
Its certainly possible to edit a text file prior to importing it into Access.

You can use either of the methods in the SO links provided by vba_php though I'd personally replace a quote = Chr(34) with the rarely used pipe symbol = Chr(124) rather than a comma Chr (44). That makes it easy to reverse the changes after import.

However I'm unclear how you're doing the import as I can import text files including quotes without any issues. The screenshot below shows a sample text file together with two different versions of the imported text - imported to a single field OR split into separate words.

attachment.php


Both were done using the import text tool without any replacements to the original text
 

Attachments

  • ImportTextWithQuotes.PNG
    ImportTextWithQuotes.PNG
    49.4 KB · Views: 605
isladogs said:
However I'm unclear how you're doing the import as I can import text files including quotes without any issues.
Click to expand...
no arguments here. smig, access should not omit quotation marks when importing.
 
isladogs said:
Its certainly possible to edit a text file prior to importing it into Access.

You can use either of the methods in the SO links provided by vba_php though I'd personally replace a quote = Chr(34) with the rarely used pipe symbol = Chr(124) rather than a comma Chr (44). That makes it easy to reverse the changes after import.

However I'm unclear how you're doing the import as I can import text files including quotes without any issues. The screenshot below shows a sample text file together with two different versions of the imported text - imported to a single field OR split into separate words.

attachment.php


Both were done using the import text tool without any replacements to the original text
Click to expand...

Thanks,
I had some ImportErrors
I noticed they all had Quote in them.

I use the DoCmd.TransferText acImportDelim to import the file
using the acImportFixed require the Specification file.
I import all data into a single field.
 
smig said:
Thanks,
I had some ImportErrors
I noticed they all had Quote in them.

I use the DoCmd.TransferText acImportDelim to import the file
using the acImportFixed require the Specification file.
I import all data into a single field.
Click to expand...

To import into a single field, you would need acImportDelim anyway.
It should work with single or double quotes.
Have you tried doing this with a saved specification?

If you are still having issues try uploading a cut down copy of your dB and a sample text file
 
Smig
Another thing to realise is that if you use the newer Import/Export tasks wizard to save your 'specification', it is no longer saved to MSysIMEXSpecs.

To recall a saved task programmatically you would instead use this code
Code: 
 DoCmd.RunSavedImportExport "SavedTaskNameHere"

This works for any saved task - import/export/link
 
I dislike editing raw files.

What you could is read the text file in a row at a time, and make any necessary changes after reading in each line.

You would then have to process each line manually in code, but it's a good exercise, and I have no doubt you would re-use the code in future.
 
Last edited:
Dave
I agree that its normally a bad idea to edit the original file
However, using either of the approaches already covered in the SO links or just importing the file direct as I suggested, there should no need to modify the contents of the text file anyway!
 
Last edited:
The issue of importation leading to quoting problems makes me wonder about the timing of these problems. Quotes will not give you issues until you try to use them in VBA expressions involving substitution, because at that point, the VBA parser's rules kick in.

But you should be able to directly read the file using OPEN FOR INPUT and using INPUT LINE. How you parse that is a different question, but I have done that several times for certain data analysis routines for the Navy and quoting characters NEVER gave me any grief whatsoever.
 
Thank you all for your comments

I did several test by putting a test line into my TXT file and there is no doubt the quote cause this line to fail, when using DoCmd.TransferText method.

I'll try using OPEN FOR INPUT to read the file.
 
Hmm... I'd be interested to see what your text file looks like.
Can you upload it here for testing?

Also please check my comment in post #8 before you spend time creating a procedure using OpenFile to do this.
 
smig said:
Thank you all for your comments

I did several test by putting a test line into my TXT file and there is no doubt the quote cause this line to fail, when using DoCmd.TransferText method.

I'll try using OPEN FOR INPUT to read the file.
Click to expand...
Hi. Just a guess but maybe you're dealing with a smart quote?
 
isladogs said:
Hmm... I'd be interested to see what your text file looks like.
Can you upload it here for testing?

Also please check my comment in post #8 before you spend time creating a procedure using OpenFile to do this.
Click to expand...

I'm not using any Import/Export wizard
 
smig said:
I'm not using any Import/Export wizard
Click to expand...

I realise that.
I'm suggesting you try that using the delimited option, save it as an import data task then call it in code as explained in post #8.
I believe it will work. If not you've only wasted a couple of minutes trying it
 
Attached the file I'm trying to read

errors are in lines:
Code: 
848
849
946
1065
1066
1067
1147
1151
1205
1314
1329
1335
1361
1364
1381
1382
1389
1408
1412
1438
1439
1457
1462
1470
1471
1477
1522
1524
1527
1531
1536
1571
1629
1631
1666
1677
1678
1681
1682
1696
1699
1712
1724
1739
1740
6294
7191
7387
7472
7487
8129
8214
8747
8817
9284
9431
9450
9618
9636
9653
10015
10506
 

Attachments

Do I assume you didn't try what I suggested after all?

That took me less than 2 minutes to download, import using the wizard with no reported errors, save the import specification and create a sub for future use.

I'll leave you to check if the lines listed are correct but as there were no import errors I see no reason why not
 

Attachments

You must log in or register to reply here.

Similar threads

P
Solved winscp
2
Replies
23
Views
753
prasadgov
P
A
Solved How to define a conditional string in VBA
Replies
3
Views
358
cheekybuddha
cheekybuddha
E
open saved import external data vba code
2
Replies
23
Views
667
DickyP
DickyP
D
Call a button's action from VBA
Replies
4
Views
194
Dave E
D
oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
254
oxicottin
oxicottin

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom