I am attempting to import a Comma Separated Value (CSV) file into an MS 
Access 2007 mdb table.  The table consists of text and numeric (defined as 
Double) fields.

Unfortunately, negative amounts are NOT enclosed by double quotes in the CSV 
file, instead the are enclosed with parenthesis signs.

Example:

"10,485.16","24",(42),"16.35",(835.33)

Currently, when I import the CSV file, the negative amounts are NOT imported.

I need assistance with how to import these negative amounts.

Thanks in advance.

"ND Pard" <NDPard@discussions.microsoft.com> wrote in message 
news:1EB07877-22EC-4366-A2CF-4D58FA29403F@microsoft.com...
>I am attempting to import a Comma Separated Value (CSV) file into an MS
> Access 2007 mdb table.  The table consists of text and numeric (defined as
> Double) fields.
>
> Unfortunately, negative amounts are NOT enclosed by double quotes in the 
> CSV
> file, instead the are enclosed with parenthesis signs.
>
> Example:
>
> "10,485.16","24",(42),"16.35",(835.33)
>
> Currently, when I import the CSV file, the negative amounts are NOT 
> imported.
>
> I need assistance with how to import these negative amounts.
>
> Thanks in advance.

Here's one way:

First load the entire file into a string
Then replace all instances of left paranthesis with minus sign
Then replace all instances of right paranthesis with zero-length string
Save the string as a new file (or overwrite the original) and import that.

Dim f As Integer, buf As String

Open "c:\temp\myFileName.csv" For Binary Access Read As f
buf = Space$(LOF(f))
Get #f, , buf
Close f
buf = Replace(buf, "(", "-")
buf = Replace(buf, ")", "")
Open "c:\temp\myFileName1.csv" For Binary Access Write As f
Put #f, , buf
Close f

Thanks Stuart.  Unfortunately I was unable to run your subprocedure in 
Access.  I am not sure why not; however, your solution allowed me to write 
the following procedure using the File System Object (or FSO) that did work.

Sub Replace_Negative_TextStream()
'This subprocedure reads the selected CVS file one line at a time.
'It then replaces any  ,(  with  ,"-  in the line read
'and replaces any  ),  with  ",   in the line read.
'Thus, a negative number that looked like:  ,(42.84),  will not appear as  
,"-42.84",
'Finally, it writes the revised line to the text file: 
BUS-BillHistory_NegRevised.csv

'set a reference to: Microsoft Scripting Runtime via Tools | References
    Dim fso As FileSystemObject
    Dim tsIN As TextStream
    Dim tsOUT As TextStream
    Dim strLineRead As String
    Dim strFileToRead As String
    Dim FLD As Object
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    
    strFileToRead = "C:\temp\myFileName1.csv"
    
    Set fso = New FileSystemObject
    
    Set FLD = fso.GetFolder("C:\Temp\")
    'Debug.Print FLD.Type
  
    'Re-write the file to a new CVS text file 
    Set tsOUT = FLD.CreateTextFile("myFileName1_NegRevised.csv", True)
    'If the above value is True if the file can be overwritten; 
    'False if it can't be overwritten. 
    'If omitted, existing files are not overwritten.
    
    Set tsIN = fso.OpenTextFile(strFileToRead, ForReading)
    
    While Not tsIN.AtEndOfStream
        strLineRead = tsIN.ReadLine
        strLineRead = Replace(strLineRead, ",(", ",""-")
        strLineRead = Replace(strLineRead, "),", """,")

        With tsOUT
            .Write strLineRead
            .Write Chr(13)
        End With
    Wend
    
    tsOUT.Close

    Set tsIN = Nothing
    Set tsOUT = Nothing
    Set FLD = Nothing
    Set fso = Nothing
    
End Sub

"ND Pard" <NDPard@discussions.microsoft.com> wrote in message 
news:E5C4638C-9900-494C-8AE1-822F72D07A93@microsoft.com...
> Thanks Stuart.  Unfortunately I was unable to run your subprocedure in
> Access.  I am not sure why not; however, your solution allowed me to write
> the following procedure using the File System Object (or FSO) that did 
> work.
>
> Sub Replace_Negative_TextStream()
> 'This subprocedure reads the selected CVS file one line at a time.
> 'It then replaces any  ,(  with  ,"-  in the line read
> 'and replaces any  ),  with  ",   in the line read.
> 'Thus, a negative number that looked like:  ,(42.84),  will not appear as
> ,"-42.84",
> 'Finally, it writes the revised line to the text file:
> BUS-BillHistory_NegRevised.csv
>
> 'set a reference to: Microsoft Scripting Runtime via Tools | References
>    Dim fso As FileSystemObject
>    Dim tsIN As TextStream
>    Dim tsOUT As TextStream
>    Dim strLineRead As String
>    Dim strFileToRead As String
>    Dim FLD As Object
>    Const ForReading = 1, ForWriting = 2, ForAppending = 8
>
>    strFileToRead = "C:\temp\myFileName1.csv"
>
>    Set fso = New FileSystemObject
>
>    Set FLD = fso.GetFolder("C:\Temp\")
>    'Debug.Print FLD.Type
>
>    'Re-write the file to a new CVS text file
>    Set tsOUT = FLD.CreateTextFile("myFileName1_NegRevised.csv", True)
>    'If the above value is True if the file can be overwritten;
>    'False if it can't be overwritten.
>    'If omitted, existing files are not overwritten.
>
>    Set tsIN = fso.OpenTextFile(strFileToRead, ForReading)
>
>    While Not tsIN.AtEndOfStream
>        strLineRead = tsIN.ReadLine
>        strLineRead = Replace(strLineRead, ",(", ",""-")
>        strLineRead = Replace(strLineRead, "),", """,")
>
>        With tsOUT
>            .Write strLineRead
>            .Write Chr(13)
>        End With
>    Wend
>
>    tsOUT.Close
>
>    Set tsIN = Nothing
>    Set tsOUT = Nothing
>    Set FLD = Nothing
>    Set fso = Nothing
>
> End Sub

Glad you got it working, but I'm curious as to why my code 'didn't work'. 
Did it compile? Did you get an error? If so, on which line?

That's if you remember what happened before writing your version :-)