Stuart McLachlan
stuart at lexacorp.com.pg
Wed Apr 20 16:37:35 CDT 2011
One useful "flat file tweak" is when you are supplied with a Unix file that has LF as line
terminators instead of CRLF. You need to convert such files before you can read them in
with Line Input.
Here's a simple function to read the whole file into memory and change LFs to CRLFs.
It works fine as long as the export file doesn't exceed about 2 billion characters, which is the
limit of a single string length. :-)
There are lots of other things you can do in a similar vein. You could for instance parse the
file into an array of lines instead using strMyArray() = Split(strTemp,Chr$(10))
Function UnixToDosFile(filename) As Long
Dim fileroot As String
Dim fileext As String
Dim newfile As String
Dim ff As Long
Dim flen As Long
Dim strTemp As String
'Create a name for the output file
fileext = Right$(filename, Len(filename) - InStrRev(filename, "."))
fileroot = Left$(filename, InStrRev(filename, ".") - 1)
newfile = fileroot & "-DOS." & fileext
'Get a file handle
ff = FreeFile
'Load complete Unix file into memory
Open filename For Binary As #ff
flen = LOF(ff) ' how big is the file
strTemp = Space$(flen) 'make our buffer the same size.
Get #ff, , strTemp 'get the file into memory
Close #ff
'Replace LF with CRLF
strTemp = Replace(strTemp, Chr$(10), Chr$(13) & Chr$(10))
'Save new file
Open newfile For Binary As #ff
Put #ff, , strTemp
Close #ff
End Function
--
Stuart
On 20 Apr 2011 at 15:49, Brad Marks wrote:
> All,
>
> Thanks for the advice and insights.
>
> I am planning to experiment with the ideas posted.
>
> I like the sounds of the "roll your own" export (I am a product of the
> 1960s :-)
>
> I have never used Access to tweak a flat file, so that also is
> something that sounds interesting, at least to have in the
> bag-of-tricks. (If someone has a simple example that they are willing
> to share, I would appreciate it).
>
> Thanks again,
> Brad
>
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
> McLachlan Sent: Wednesday, April 20, 2011 4:54 AM To: Access
> Developers discussion and problem solving Subject: Re: [AccessD]
> Problem Exporting to a CSV file with a Column Namethat has a #
>
> Hi Brad,
>
> Listen to Gustav ;-)
>
> Especially for import. You willl find all sorts of CSV file formats.
> The biggest problem is the inconsistent use of "quotes". Sometime all
> dates and strings are quoted, sometimes they are only quoted if they
> contain a comma, sometimes you will get numbers quoted because they
> are formatted to include commas and others which are not because they
> don't include commas, sometimes you will get embedded real quotes
> represented as double quotes. You can get all of these variations in
> a single file. The built in Import function can't handle many of
> them.
>
> What I do is import the line, strip the quotes and convert it into Tab
> delimited strings without extraneous quotes.
>
> Function ConvertLine(strIn As String) As String
> 'Converts CSV(either quoted or non-quoted text) to TAB delimited
> Dim inquotes As Boolean Dim strC As String Dim x As Long
>
> 'Change real delimiter commas to Tabs
> 'ie those that aren't inside quotes
> For x = 1 To Len(strIn)
> strC = Mid$(strIn, x, 1)
> If strC = "," And Not inquotes Then
> Mid$(strIn, x, 1) = Chr$(9)
> End If
> If strC = """" Then
> inquotes = Not inquotes
> End If
> Next
>
> ' there may be real quotes escaped as "paired" quotes
> ' so change them temporarily to a non print character
> strln = Replace(strln,"""""",chr$(1))
>
> 'get rid of all the remaining quotes
> strln = Replace(strIn, """", "")
>
> 're-instate the escaped quotes
> ConvertLine = Replace(strln,Chr$(1),"""")
> End Function
>
> Then I split the record using
> ...
> Line Input #ff strInputLine
> strData() = Split(Convertline(strInputLine),Chr$(9))
> ...
>
> Once you have done that, you can convert the strings to other data
> types as required.
>
> --
> Stuart
>
> On 20 Apr 2011 at 9:59, Gustav Brock wrote:
>
> > Hi Brad
> >
> > Listen to Stuart.
> > Once you have this set up you are in total control and will never
> > look back. The same goes for importing text files.
> >
> > /gustav
> >
> >
> > >>> stuart at lexacorp.com.pg 20-04-2011 05:40 >>>
> > The best solution is not to use the built in Access export routine.
> > Roll your own and you can put anything you want in the file.
> >
> > Aircode:
> >
> > Function Export() as Long
> > Dim rs as DAO.Recordset
> > Dim ff as long
> > ff = Freefile
> > set rs = CurrentDB.Openrecordset("qryMyExport")
> > ff = Freefile
> > Open "myExportFile.csv" for Output as #ff
> > Print #1,"First fieldname,Employee #,Third fieldname"
> > While not rs.Eof
> > Print #1 rs(0) & "," & rs(1) & "," & rs(2)
> > rs.movenext
> > Wend
> > Close #ff
> > End Function
> >
> > --
> > Stuart
> >
> > On 19 Apr 2011 at 17:23, Brad Marks wrote:
> >
> > > All,
> > >
> > > We are just starting to use an Access 2007 application to feed
> > > data to another outside system via a CSV file.
> > >
> > > This outside system is very rigid and we need to use exact field
> > > names on the CSV file that we are creating.
> > >
> > > The catch is that one of the fields is call "Employee #".
> > >
> > > We set up an Export Specification (called Export-Query1 for our
> > > testing) and we are using the following command to do the export
> > >
> > > DoCmd.RunSavedImportExport "Export-Query1"
> > >
> > > When we run the application, everything works nicely, except the
> > > field named "Employee #" is somehow being renamed "Employee ." in
> > > the generated CSV file.
> > >
> > > We have run a number of tests and it looks like we have stumbled
> > > upon either a "bug" a "feature", or something that we just don't
> > > understand.
> > >
> > > Is there a way to force Access to not replace the "#" with a "."
> > > ?
> > >
> > > Thanks for your help,
> > >
> > > Brad
> >
> >
> > --
> > 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
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>