CSV File

[Martin]

I have a comma-separated file that contains Part Numbers and Descriptions.
The description field can contain the double-quote(") character to represent
"inches". For example, 2" would mean 2 inches.

When I import this file to Access, these imbedded double-quotes cause import
errors. If I import the same file to Excel, they do not. These extra
characters are dropped from the data althogether.

Is there a way to make Access act like Excel when importing this file? Or
is there something in the Access import definition that I am missing?

Thanks.

 

[tina]

i created a small .csv file (from Excel) that looks like this when opened in
Notepad:

a,"1"""
b,"2"""
c,"3"""

it easily imported into Access as

Field1 Field2
a 1"
b 2"
c 3"

both fields imported as text data type fields.

can you paste a few rows of your .csv file into a post, so we can see what
it looks like? (if the data is sensitive, just replace it with dummy values
while retaining the formatting characteristics of the .csv file.) and btw,
what version of Access are you using?

hth

 

[Ken Snell [MVP]]

You can tell ACCESS to not treat " as a text identifier in the Import
Specification that you create for importing this file. In the Advanced
window of the import wizard, select Delimited and then set Text Qualifier to
{none}.

 

[Martin]

Thank you both for the responses

An couple of examples of a the Part Number and Desription fields is as
follows:
"TAPE","Adhesive Tape 2""
"PADDING","Foam Padding 3"x4" Styro"

I tried Ken's suggestion, and I get Type Conversion errors in the Import
Errors table. I believe I need the text fields delimited because there could
be commas in the descriptions (at least that is what I was told.)

Thanks again.

 

[Ken Snell [MVP]]

You may need to use VBA code to read in the data and write it to your table,
instead of doing a direct import.

Post four or five full records from the text file and let's see what we can
help you write.

 

[Martin]

Here are a few lines. Unfortunately, this is wrapping. Each record is shown
here as three lines. I put the blank lines to separate the records. Just
concantenate the lines to get the full record.

Thanks for the help.

"35","GAUZE 1X36"
84412600","3595","xxxxxxxxxxxxxx","EA","000000.512","DZ","12","000006.140","113718","718","0","","0"

"38","BANDAGE 2"
MDS087002LF","3595","xxxxxxxxxxxxxx","EA","000000.435","CA","50","000021.760","113718","718","718","BANDAGE ACE 2" (000038)","531020"

"846","POUCH 5 1/4"X10"
PP10525","3595","xxxxxxxxxxxxxx","BX","000011.112","CA","10","000111.120","113718","718","0","","0"

"568","BANDAGE PLASTER
4"","3595","xxxxxxxxxxxxxx","EA","000007.020","CA","12","000084.240","113718","718","718","BANDAGE PLASTER 4"","516490"

 

[John Nurick]

Since every field is enclosed in quotes it's easy to convert to a
tab-separated file with no quotes, which Access will be able to import.
Here's a little VBA procedure that should do it:

Public Sub Fixit(InputFileSpec As String, _
OutputFileSpec As String)

Dim lngIn As Long, lngOut As Long
Dim strLine As String

lngIn = FreeFile()
Open InputFileSpec For Input As #lngIn
lngOut = FreeFile()
Open OutputFileSpec For Output As #lngOut

Do Until EOF(lngIn)
Line Input #lngIn, strLine
'dump initial and terminal quotes
strLine = Mid(strLine, 2, Len(strLine) - 2)
'change to tab-separated
strLine = Join(Split(strLine, ""","""), vbTab)
Print #lngOut, strLine
Loop

Close lngIn
Close lngOut

End Sub

 

[Ken Snell [MVP]]

I have not fully tested this subroutine, but it should be close. I have
"air-code" adapted it from an EXCEL macro that I have written previously to
read in text files.

This sub is intended to read data from your text file (note that you need to
provide the path in the code) and to ignore embedded " characters, and then
to build each field's data and append new records to the table. It may have
a few bugs in it yet, so test it carefully.


Public Sub ReadYourData()

Dim blnWrite As Boolean, blnDel As Boolean

Dim dbs As DAO.Database

Dim intFile As Integer, intCol As Integer, intCut As Integer
Dim intCount As Integer

Dim rst As DAO.Recordset

Dim strFieldValue As String, strLine As String, strM As String

Const strDelimiter As String = ","

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("NameOfTableToGetData", dbOpenDynaset,
dbAppendOnly)

intFile = FreeFile()
Open "PathtoYourTextFile.txt" For Input As #intFile

'read each record from the text file until "end of file" is found
Do While EOF(intFile) = False
'reset intCol to default value (zero)
intCol = 0
'read record from text file
Line Input #intFile, strLine
'get length of record that was read in
intCut = Len(strLine)

' start new record in table
rst.AddNew

'set starting position within strLine to default value (one)
intCount = 1
Do While intCount <= intCut
blnWrite = False
'"blank" out strFieldValue so that it can be concatenated in order
'to get the value of the next field
strFieldValue = ""
'build a "field" until a delimiter character is found
Do While blnDel = False And intCount <= intCut
'get single character from record
strM = Mid(strLine, intCount, 1)
'check to see if character is the delimiter character
If strM <> strDelimiter Then
'character is not the delimiter character
'check to see if character is equal to " character (Chr(34)) and if
' just previous character was a delimiter (meaning, this is the
' first character of new field)
If strM = Chr(34) And (Mid(strLine, _
IIf(intCount = 1, 1, intCount - 1), 1) _
= strDelimiter Or intCount = 1) Then
'character is the " character
'find the matching " character in the string
Read_Char:
'concatenate character onto field string
strFieldValue = strFieldValue & strM
'increment character location counter
intCount = intCount + 1
'get next character
strM = Mid(strLine, intCount, 1)
'check to see if character is equal to " character
If strM <> Chr(34) And intCount < intCut And _
Mid(strLine, intCount + 1, 1) <> strDelimiter
Then
'continue reading characters because this one isn't the
' field-ending " character
GoTo Read_Char
Else
'found ending " character, so finish concatenating this field
strFieldValue = strFieldValue & strM
'increment character location counter
intCount = intCount + 1
End If
Else
'concatenate character to field
strFieldValue = strFieldValue & strM
'increment character location counter
intCount = intCount + 1
'set flag to show that this character is not a delimiter character
blnDel = False
End If
Else
'set flag to show that this character is a delimiter character
blnDel = True
End If
Loop
blnDel = False
rst.Fields(intCol) = strFieldValue
intCol = intCol + 1
intCount = intCount + 1
blnWrite = True

Loop
'if found end of record before the current field has been written
' to the table's field, write the field to the table's field
If blnWrite = False Then rst.Fields(intCol) = strFieldValue
rst.Update

' continue until EOF is found
Loop

' close text file
Close #intFile

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Sub



--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

John - he'd mentioned that there may be embedded commas within a field's
text string. That would create a problem with the Split function in this
case.

 

[Ken Snell [MVP]]

John - he'd mentioned that there may be embedded commas within a field's
text string. That would create a problem with the Split function in this
case.

However, with your use of "," as the "delimiter", this would be a problem
only if that embedded comma were also surrounded by " characters... which is
probably not very likely.

So, I like your idea!

 

[Martin]

Thank you all for the feedback. I will try this VBA code idea to see if I
can get it to work.
I am new to VBA, but I will give it a shot and get back if I have problems.

Thanks.

 

[John Nurick]

However, with your use of "," as the "delimiter", this would be a problem
only if that embedded comma were also surrounded by " characters... which is
probably not very likely.

Yes, this takes advantage of the fact that every single field in this
particular file is enclosed in quotes, which means that the field
separator is in effect the three characters "," . Otherwise, parsing
would be difficult or impossible, because the file uses " both as a
character in the data and as a field delimiter ("text qualifier", in
Microsoft language).

The technique won't work for CSV files in general, where fields need not
be quoted unless they actually contain a comma. On the other hand, a
well-formed CSV file escapes any quote marks in the actual data.
Microsoft software likes them doubled (as in VBA literal strings), so
this data
BANDAGE, ELASTIC 5"X10"
would be represented as
"BANDAGE, ELASTIC 5""X10"""
Other software tends to use a backslash:
"BANDAGE, ELASTIC 5\"X10\""
If quotes in the data aren't escaped, parsing can become impossible.
With a string such as
...,"BANDAGE, ELASTIC 24", 48", 60" & 72"",...
the ", after 24" is a valid end-of-field sequence.

So, I like your idea!

Thanks!