Help, how do I import a CSV file when Negatives are in Parenthesis

[ND Pard]

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.

 

[Stuart McCall]

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

 

[ND Pard]

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 = fspenTextFile(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

 

[Stuart McCall]

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 = fspenTextFile(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