philkryder
asked on
MS ACCESS 2010 - import a CSV with character numeric data "=""123"""
I have a CSV file with character numeric data.
I would like to import it into MS ACCESS.
The format doubleQuote and equals sign in front of two doubleQuotes at the front and three double quotes after each field.
here is an example:
Field1,Field2,Field3
"=""013""","=""0133530670-
This format allows the CSV to be opened in Excel nicely with each field contain numbers, left justified as "characters".
How can I import them into MS ACCESS 2010.
I've tried using an import specification with comma delimiter and double quote " as the separator.
When I do that, I get values like
="013"
thanks!
Phil
I would like to import it into MS ACCESS.
The format doubleQuote and equals sign in front of two doubleQuotes at the front and three double quotes after each field.
here is an example:
Field1,Field2,Field3
"=""013""","=""0133530670-
This format allows the CSV to be opened in Excel nicely with each field contain numbers, left justified as "characters".
How can I import them into MS ACCESS 2010.
I've tried using an import specification with comma delimiter and double quote " as the separator.
When I do that, I get values like
="013"
thanks!
Phil
I was able to get this to work with 2013, I assume 2010 will be the same. What I did was take off the extra double-quotes, so that the data was shown just like you want it to import.
="013",="0133530670-01","=
Then during the import, set the Delimiter as Comma and the Text Qualifier to None
Give that a try,
0Doc
="013",="0133530670-01","=
Then during the import, set the Delimiter as Comma and the Text Qualifier to None
Give that a try,
0Doc
TIP: Don't assume backward compatibility between versions. I find that you generally can expect things to work in newer versions.
I did a test and import the data first into a table as text fields. I ran an update query using:
Concept test results:
I did a test and import the data first into a table as text fields. I ran an update query using:
Mid(Replace([MyFiledNameHere],Chr(34),""),2)Concept test results:
strNum = "=""123"""
? strNum
="123"
? Mid(Replace(strNum,Chr(34),""),2)
123ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this:
Public Function Q_28318074()
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim I As Integer
Dim StringArray() As String
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("MyTableName") '<-- Change to your tablename
'Opening the file to read from
FileNum = FreeFile()
InputFile = "C:\MyFolder\MyTextFile" '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
I = 0
Do Until EOF(FileNum) = True
Line Input #FileNum, InputString 'Read the data in
StringArray = Replace(Split(InputString, Chr(44)), Chr(34), "")
With RS 'the input is an empty string write it
.AddNew
For I = 0 To (UBound(StringArray) - 1)
.Fields(I).Value = StringArray(I)
Next I
.Update
End With
I = 0
Loop
RS.Close
Set RS = Nothing
Set DB = Nothing
Close #FileNum
End FunctionASKER
I loaded the data with the extra characters and then stripped them in an update query.
It worked.
I was hoping there was a way to make the specification do it.
but, sadly, no.
It worked.
I was hoping there was a way to make the specification do it.
but, sadly, no.
I was hoping there was a way to make the specification do it.
Just a note: I went away from using Import/Export specs years ago. There was one too many database corruptions that the spec was lost or had to be recreated from memory. I found that I could build a basic VBA function to do the same thing. Even some generic functions that could accept different delimiters.
Just a note: I went away from using Import/Export specs years ago. There was one too many database corruptions that the spec was lost or had to be recreated from memory. I found that I could build a basic VBA function to do the same thing. Even some generic functions that could accept different delimiters.
Did you ended up using my suggestion?
Like jimpen, I rarely (almost never) use the import specs.. It works best with very "clean" data. Unfortunately data is reraly "clean". That is why I import into a temp/work table.
I was hoping there was a way to make the specification do it.
but, sadly, no.
Like jimpen, I rarely (almost never) use the import specs.. It works best with very "clean" data. Unfortunately data is reraly "clean". That is why I import into a temp/work table.
Unfortunately data is rarely "clean".
I had one text file that the Ops team was importing that came from an IBM or *nix format. It would have multiple EOF characters that would choke in a Win/DOS environment; but not be there every time.
I had to build a character by character import that found if the EOF character occurred before the true end of the file (hint it was based on file sizes v. character count) and have the operator do a search and replace on the text file.
I wonder what a newbie Access dev using an IMEX spec would have done with it?
I had one text file that the Ops team was importing that came from an IBM or *nix format. It would have multiple EOF characters that would choke in a Win/DOS environment; but not be there every time.
I had to build a character by character import that found if the EOF character occurred before the true end of the file (hint it was based on file sizes v. character count) and have the operator do a search and replace on the text file.
I wonder what a newbie Access dev using an IMEX spec would have done with it?
Once you have the imported into text fields you can run an update query to scrip off the =.