Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS ACCESS 2010 - import a CSV with character numeric data "=""123"""

Posted on 2013-12-13
9
Medium Priority
?
867 Views
Last Modified: 2013-12-21
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-01""","=""0010000101847"""

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
0
Comment
Question by:philkryder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 21
ID: 39718142
The way I always import in to import the data into a temp/work table. This give me a chance to "scrub" the data before appending it tot he final destination.

Once you have the imported into text fields you can run an update query to scrip off the =.
0
 
LVL 3

Expert Comment

by:0Doc
ID: 39718144
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","=0010000101847"
Then during the import, set the Delimiter as Comma and the Text Qualifier to None
Give that a try,

0Doc
0
 
LVL 21
ID: 39718151
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:

Mid(Replace([MyFiledNameHere],Chr(34),""),2)

Open in new window


Concept test results:
strNum = "=""123"""
? strNum
="123"
? Mid(Replace(strNum,Chr(34),""),2)
123

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39718276
If you can change the file format to the standard of comma delimited with text in double quotes, it will import into Access using TransferText.  With a non-standard format, you either need to import it using VBA where you examine the file character by character or sometimes you can import it and then fix it up later with queries.

In a standard format, the record would be:
"013","0133530670-01","0010000101847"
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39720312
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 Function

Open in new window

0
 
LVL 1

Author Comment

by:philkryder
ID: 39732539
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.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39732580
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.
0
 
LVL 21
ID: 39734173
Did you ended up using my suggestion?

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.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39734190
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?
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question