backslash stripped quotations

[Korizon67]

I have a CSV file that has always worked but recently the file we were getting was changed to have " " around the text fields, Now its also adding \"data|\" in some places... access is ignoring anything after the first "\.

I cant seem to find anything in access to get around this, Any ideas? Right now i use access to read the csv and spit it to mysql then php to move data around... it always worked great till they added the quotes...

something like "joe","Shmoe","address","zip"; but some ppl are adding things they like to be called like this "Joseph" \"joe\"","shmoe"... Access is dieing on the \"

Thanks

mike

 

[Korizon67]

I actually figured out the problem, Access requires quotes to be escaped by quotes, it worked when i did a find replace \" with "" but now i guess i need to filter it in access somehow... any ideas on how to force access to read \" as "" ?

Thank You

Mike

 

[Remou]

Are these files large? Is it feasible to read in the file using a TextStream (FileSystemObject) or a combination of TextStream and import?

 

[Korizon67]

They are not very large, What they are is a daily contact csv from our website, we get usually 175 to 200 leads a day, we recently switched to a php based system for our call ppl, we left the automatic import that we always used in access to import the table from csv to access then access to Mysql... because frankly we are still learning php/mysql...

the quotes were recently added from our web dev because we added a couple fields to track the key words used and referring domain... i guess in order for the cvs to come out correctly he had to add a text qualifier i.e. "data" this completely screwed our importing method.

 

[Remou]

Very roughly:

Set fs = CreateObject("Scripting.FileSystemObject")

strFile = DT & "File.txt"
strFileOut = DT & "FileOut.txt"
Set f = fs.opentextfile(strFile)
a = f.readall
a = Replace(a, "\", Chr(34))

Set o = fs.createtextfile(strFileOut)
o.write a
o.Close

DoCmd.TransferText acImportDelim, , "tblCSV", strFileOut

 

[Korizon67]

Set fs = CreateObject("Scripting.FileSystemObject")

strFile = DT & "File.txt"
strFileOut = DT & "FileOut.txt"
Set f = fs.opentextfile(strFile)
a = f.readall
a = Replace(a, "\", Chr(34))

Set o = fs.createtextfile(strFileOut)
o.write a
o.Close

is this to make the table?? if so i can do away with it i think, access works fine for that >>> DoCmd.TransferText acImportDelim, , "tblCSV", strFileOut

also the way it is , it replaces all \s in the file, some are needed i only need to replace ones that are \" is this possible like maybe a = Replace(a, "\\"", "\"\"") or something? sry i am a dolt at VB

 

[Remou]

The idea is to read in the file and replace the back slasheswith quotes and the write the changed data out to disk for import using TransferText. Access accepts double quotes for a quote contained within a quote.

As for the Replace, try:

[tt]a = Replace(a, "\" & Chr(34), Chr(34) & Chr(34))[/tt]

 

[Korizon67]

Gotcha, But this is a medical website and has to do with lumbar regions like LS\CS\TS so it is making those LS"CS"TS

I go it to work using

a = Replace(a, "\""", """""")

But i will do it the way you said because i am sure its the correct way thank you VERY much, you are a lifesaver