David Emerson
newsgrps at dalyn.co.nz
Thu Oct 16 19:20:40 CDT 2008
Finally had a chance to test this. First my confession - The database is Access 97 so split isn't available (sorry not to mention this before) I ended up using Steve's suggestion and sett up an import specification which imported the records into a temporary table. This maintained the commas when they were part of the text. This also saved having to parse through the records as the single quotes were automatically removed. Thanks everyone for your help. David At 14/10/2008, you wrote: >Here's my test. It exercises the import and export options of >DoCmd.TransferText and also explores the impact of having a variable number >of fields in the text file. The board has been doing strange things to the >format of some of my messages, so I hope this one is legible. >First create a text file called "Table1.csv" with the following content: >"ID","T1","T2" >1,"T11","T21" >2,"T2,2","T22" >3,"T32","T3,2" >4,"T4,1","T4,2" >5,"T4""1","T5'2" >First create a text file called "TableZ.csv" with the following content: >"ID","T1","T2" >1,"T11","T21" >2,"T2,2","T22" >3,"T32","T3,2" >4,"T4,1" >5,"T5""1","T5'2","T4'3" >Then run this code: >Sub testCSV() >sPath = CurrentProject.Path & "\" >On Error Resume Next >DoCmd.DeleteObject acTable, "Table1" >DoCmd.DeleteObject acTable, "Table1A" >DoCmd.DeleteObject acTable, "Table1X" >DoCmd.DeleteObject acTable, "Table1Z" >On Error GoTo 0 >DoCmd.TransferText acImportDelim, , "Table1", sPath & "Table1.csv", True >DoCmd.TransferText acImportDelim, , "Table1Z", sPath & "Table1Z.csv", True >DoCmd.TransferText acExportDelim, , "Table1", sPath & "Table1A.csv", True >DoCmd.TransferText acImportDelim, , "Table1A", sPath & "Table1A.csv", True >DoCmd.TransferText acExportDelim, , "Table1Z", sPath & "Table1Y.csv", True >DoCmd.TransferText acImportDelim, , "Table1Y", sPath & "Table1Y.csv", True >End Sub >Then inspect the tables and the files. Please let me know if this does what >you want it to. >Regards, >Steve Goodhall, PMP >248-505-5204 mobile > > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson >Sent: Saturday, October 11, 2008 10:22 PM >To: Access Developers discussion and problem solving >Subject: Re: [AccessD] Exporting/Importing CSV files > >You may have a good point there. I will do some testing at this end as >well. > >Thanks > >David > >At 10/12/2008, you wrote: > >I understand that, but it still gives you the appropriate format for a CSV. > >I may have some code from my Master's Thesis Project that will handle this. > >I will look for it when I get home. > >Also, I think that you could use DoCmd.TransferText or > >docmd.TransferSpreadsheet to invoke this functionality in code. I will try > >that out and check how different number of fields will affect it. > >Regards, > >Steve Goodhall, PMP > >248-505-5204 mobile > > > > > >-----Original Message----- > >From: accessd-bounces at databaseadvisors.com > >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson > >Sent: Saturday, October 11, 2008 3:24 PM > >To: Access Developers discussion and problem solving > >Subject: Re: [AccessD] Exporting/Importing CSV files > > > >This would work if I was using the menus to export, import, but > >everything is done by code to handle rows with different numbers of > >fields in them. I think Drew's (final) solution may be the one to use. > > > >Thanks all for your input > > > >David > > > >At 10/11/2008, you wrote: > > >I checked this by exporting and importing a file using Menu, File, Export > > >and Menu, File, Get External Data. I start with a table that looks like > > >this: > > >Table1 > > >ID T1 T2 > > >1 T11 T21 > > >2 T2,2 T22 > > >3 T32 T3,2 > > >4 T4,1 T4,2 > > >5 T4"1 T5'2 > > > > > >I export it to a CSV format which looks like this: > > >"ID","T1","T2" > > >1,"T11","T21" > > >2,"T2,2","T22" > > >3,"T32","T3,2" > > >4,"T4,1","T4,2" > > >5,"T4""1","T5'2" > > >I import it and I get: > > >Table1A > > >ID T1 T2 > > >1 T11 T21 > > >2 T2,2 T22 > > >3 T32 T3,2 > > >4 T4,1 T4,2 > > >5 T4"1 T5'2 > > > > > >As you see, commas inside double quotes are not a problem. > > >I also tried double quotes and single quotes. Double quotes within the > > >field need to be changed to 2 double quotes when writing the CSV file. > > >Regards, > > >Steve Goodhall, PMP > > >248-505-5204 mobile > > > > > >Steve Goodhall, PMP > > >248-505-5204 mobile > > > > > > > > >-----Original Message----- > > >From: accessd-bounces at databaseadvisors.com > > >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo > > >Sent: Friday, October 10, 2008 1:19 AM > > >To: 'Access Developers discussion and problem solving' > > >Subject: Re: [AccessD] Exporting/Importing CSV files > > > > > >Is that why they are called CSV files? - <smile> > > > > > >CSV=Comma Separated Values. Not only are the "notorious" but "essential" > > > > > >Max > > > > > > > > >-----Original Message----- > > >From: accessd-bounces at databaseadvisors.com > > >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > > >Elizabeth.J.Doering at wellsfargo.com > > >Sent: 10 October 2008 03:16 > > >To: accessd at databaseadvisors.com > > >Subject: Re: [AccessD] Exporting/Importing CSV files > > > > > > > > >David, > > > > > >You may have better success removing commas from all fields, all the > > >time. CSVs are notorious for being comma separated! > > > > > >Either enforce farther back that commas can't be entered in the fields, > > >or use Replace to get rid of them as you are exporting. > > > > > >HTH, > > > > > > > > >Liz > > > > > >-----Original Message----- > > >From: accessd-bounces at databaseadvisors.com > > >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson > > >Sent: Thursday, October 09, 2008 7:11 PM > > >To: accessd at databaseadvisors.com > > >Subject: [AccessD] Exporting/Importing CSV files > > > > > >Team, > > > > > >I am wanting to export fields into a CSV file. This I can do using > > >Print #. I am also enclosing each field in single quotes thus: > > > > > >Print #lngFileHandle, "'DET','" & rst!Field1 & "','" & rst!Field2 & > > >"','" & rst!Field3 & "'" > > > > > >Result: > > >'DET','1','Joe','Bloggs','10 Kingston Lane' > > > > > >I can also read the files back into an arrany using Input #: > > > > > >Input #lngFileHandle, strData(0), strData(2), strData(3), strData(4) > > > > > >This works fine unless one of the output fields includes a comma. Eg > > >'DET','1','Joe','Bloggs','10 Kingston Lane, Jamaca' > > > > > >In this case the Input# only gets "'10 Kingston Lane" and assumes that " > > >Jamaca'" is for the next field. > > > > > >How do I allow for comma's to be included in fields? > > > > > > > > >Regards > > > > > >David Emerson > > >Dalyn Software Ltd > > >Wellington, New Zealand > > > >-- > >AccessD mailing list > >AccessD at databaseadvisors.com > >http://databaseadvisors.com/mailman/listinfo/accessd > >Website: http://www.databaseadvisors.com > > > >-- > >AccessD mailing list > >AccessD at databaseadvisors.com > >http://databaseadvisors.com/mailman/listinfo/accessd > >Website: http://www.databaseadvisors.com > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com