Editing an external text file is it possible? (1 Viewer)

smig

Registered User.
Local time
Today, 19:39
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) ?
 

isladogs

MVP / VIP
Local time
Today, 17:39
Joined
Jan 14, 2017
Messages
18,186
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.



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

Attachments

  • ImportTextWithQuotes.PNG
    ImportTextWithQuotes.PNG
    49.4 KB · Views: 503

vba_php

Forum Troll
Local time
Today, 12:39
Joined
Oct 6, 2019
Messages
2,884
However I'm unclear how you're doing the import as I can import text files including quotes without any issues.
no arguments here. smig, access should not omit quotation marks when importing.
 

smig

Registered User.
Local time
Today, 19:39
Joined
Nov 25, 2009
Messages
2,209
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.



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

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.
 

isladogs

MVP / VIP
Local time
Today, 17:39
Joined
Jan 14, 2017
Messages
18,186
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.

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
 

isladogs

MVP / VIP
Local time
Today, 17:39
Joined
Jan 14, 2017
Messages
18,186
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
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 17:39
Joined
Sep 12, 2006
Messages
15,613
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:

isladogs

MVP / VIP
Local time
Today, 17:39
Joined
Jan 14, 2017
Messages
18,186
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_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:39
Joined
Feb 28, 2001
Messages
26,999
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.
 

smig

Registered User.
Local time
Today, 19:39
Joined
Nov 25, 2009
Messages
2,209
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.
 

isladogs

MVP / VIP
Local time
Today, 17:39
Joined
Jan 14, 2017
Messages
18,186
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:39
Joined
Oct 29, 2018
Messages
21,358
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.
Hi. Just a guess but maybe you're dealing with a smart quote?
 

smig

Registered User.
Local time
Today, 19:39
Joined
Nov 25, 2009
Messages
2,209
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.

I'm not using any Import/Export wizard
 

isladogs

MVP / VIP
Local time
Today, 17:39
Joined
Jan 14, 2017
Messages
18,186
I'm not using any Import/Export wizard

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
 

smig

Registered User.
Local time
Today, 19:39
Joined
Nov 25, 2009
Messages
2,209
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

  • BKMVDATA.zip
    191 KB · Views: 161

isladogs

MVP / VIP
Local time
Today, 17:39
Joined
Jan 14, 2017
Messages
18,186
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

  • ImportTextWithQuotes.zip
    391.8 KB · Views: 159

Users who are viewing this thread

Top Bottom