Solved

Importing a csv via code

Posted on 2011-03-11
29
419 Views
Last Modified: 2012-05-11
I need some help with syntax for importing a file within code.

I'm unfamiliar with file manipulation commands.

How do I automate an import for users?  The file will always have the same format, but it won't always be named the same thing or be in the same folder location on the user's computer.

So i need the code that will:
1. prompt the user for the file location
2. import the data using my saved import ("Import-MyData")
3. let the user know if the import failed because they picked a file without the proper format.

Thanks so much for the help.
0
Comment
Question by:epuglise
  • 11
  • 8
  • 6
  • +2
29 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 35113285
Not where I can come up with working code right now.  Have to Google around a little.

If the following links don't work, please provide your Access version.

To load the file check out:
http://www.ozgrid.com/forum/showthread.php?t=32942&page=1

Check out FileDialog in:
http://www.tek-tips.com/viewthread.cfm?qid=1216492
0
 

Author Comment

by:epuglise
ID: 35113290
MS Access 2007
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113301
FileDialog is definitely available:
http://msdn.microsoft.com/en-us/library/bb243865(v=office.12).aspx

It appears so is transfer text:
http://msdn.microsoft.com/en-us/library/bb214141(v=office.12).aspx

Between those two, it should work.  I'm sorry I'm not where I can provide a complete working model.
0
 

Author Comment

by:epuglise
ID: 35113326
These links were very helpful. Thank you.

So i have the bit working that opens the file dialog box.  And I can pick a file.
and I have the bit working that does the text transfer.
But how do I "glue" the two together??

Once the user selects the file... how does my code know to import that file? Sorry to be so green...
0
 

Author Comment

by:epuglise
ID: 35113341
Ok I see the line i missed now.  I define a variable and capture the filename and path using the .SelectedItems() property.

I'm testing the code now and will post points if it works!!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113345
>>Sorry to be so green...

Never a problem.  We all started knowing nothing!

>>But how do I "glue" the two together??

You should be able to just use the variable name from the dialog box in the FileName in the call to transferText:

From the link above:
FileName: A string expression that's the full name, including the path, of the text file you want to import from, export to, or link to.

Then in the example:
DoCmd.TransferText acExportDelim, "Standard Output", _
    "External Report", "C:\Txtfiles\April.doc"

It's just a string variable so the variable from the dialog box should work fine.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113347
>>I'm testing the code now and will post points if it works!!

lol... you caught me typing...
0
 

Author Comment

by:epuglise
ID: 35113357
Hmmm... ok so i think we're almost there but... the file dialog box is acting funny. When I use my mouse to select a file, it doesn't populate the file text box in the dialog box... so no filename is getting passed to the .selecteditmes property (or perhaps that isn't the correct thing to pull the filename/path from?)

thoughts? (and thanks again-- i have learned a TON from this site!)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113373
>>or perhaps that isn't the correct thing to pull the filename/path from?

Since I can't set up my own test: Can't help there except to Google around or read through more of the docs.

If you can't figure it out I'm sure another Expert will be along soon.  Access is a pretty busy Zone.

>>and thanks again-- i have learned a TON from this site!

It's a great site!  That's why I'm here.  I think I actually learn more by helping others than I do by asking questions here or reading on my own.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
ID: 35113380
OK,

Its a CSV file, right and I assume Access 2010 because you mention 'my saved import'
I've seen those, and they are a nice improvement over straight importing in Access 2003 because you can force what data types each column will be.
Access 2003 was distressing when you had a text column that stored mostly numeric text ie 123456 and the occasional 123456-7.
It always asssumed numeric. Sigh :(

No matter.
Look here first
http://www.utteraccess.com/forum/Call-saved-import-Access-t1951727.html

I still develop in Access 2003 because I have a mixed environment and The Ribbon and layout views are painful.
Here are the two subs I used to import excel data.
What I did is create an excel sheet that had all the right columns and right data types and saved it.
I then made a link table to it.
The first sub lets the end user select any file.
That file then overwrites the file used for the link table.
The second sub then opens the newly refreshed link table and does some recordset work on it.

You may like the stuff in the posted link better -- but my stuff will give you more granular control over the import.
Your call.

Private Sub cmdImport_Click()
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

'variables to build and to hold new path that data will be saved to
Dim BuiltPath As String
Dim Success As Boolean
Dim MyInitialFileName

'variables for a recordet in tblPictures
Dim db As Database
Dim rs As Recordset

'prepare a response for a msgbox to bail out if conditions aren't met
Dim response As Integer

set db = currentdb
    
'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

response = MsgBox("The CSV file to be imported must be CLOSED before the import is attempted.  Click Yes if it is closed, Click No to exit.", vbCritical + vbYesNo, "Closed file?")
If response = vbNo Then
    Exit Sub
End If
'Use a With...End With block to reference the FileDialog object.
With fd
    .InitialView = msoFileDialogViewThumbnail
    .Title = "File Selector"
    .InitialFileName = "c:\somefolder\"
    .ButtonName = "Import"
    .AllowMultiSelect = False

    'Use the Show method to display the File Picker dialog box and return the user's action.
    
    'The user pressed the action button.
    If .Show = True Then
        response = MsgBox("If you continue, the file you just selected will be imported and processed", vbOKOnly, "Transfer and Save")
        response = MsgBox("Import and Process", vbYesNo, "Last Chance")
        If response = vbYes Then
            'check that only one file was picked
            If .SelectedItems.count > 1 Then
                MsgBox "You may only select one item.  Try again!"
                Exit Sub
            Else
                vrtSelectedItem = .SelectedItems(1)
                SetAttr vrtSelectedItem, vbReadOnly
            End If

            'see if the file exists and check for a desired overwrite
            BuiltPath = "c:\SomePermanentFolder\SomePermanentFile.csv"
            If fs.FileExists(BuiltPath) Then
                    fs.DeleteFile BuiltPath, True
                    fs.CopyFile vrtSelectedItem, BuiltPath
            Else
                'copy the file with a new name to the save location
                fs.CopyFile vrtSelectedItem, BuiltPath
            End If
        Else
            'bailed on last chance
        End If
    Else
        'bailed on the file dialog box
    End If
    
End With



If fs.FileExists(BuiltPath) Then
    'this calls the next sub which processes the newly updated file
    Success = ImportAndProcess()
    If Success = True Then
        MsgBox "Done!"
    Else
        MsgBox "The import and processing failed."
    End If
End If
End Sub

'------------------------------------------------------------------------------------

Private Function ImportAndProcess()

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim NumRecords As Integer
Dim BadRecord As Boolean
Dim x As Integer
x = 0
'okay, now open the linked table
Set db = CurrentDb
Set rs1 = db.OpenRecordset("select SomeStuff from tblGoodLinkedTableYouCreated;", dbOpenDynaset, dbSeeChanges)

rs1.MoveLast
rs1.MoveFirst
NumRecords = rs1.RecordCount
'MsgBox NumRecords
Dim AlreadyCommitted() As Long
ReDim AlreadyCommitted(NumRecords)

'Check that we have a gooder
Do Until rs1.EOF = True
    If SomeConditionYouWantToCheck = False Then
        BadRecord = True
        ImportAndProcess = False
        Exit Function
    End If

    If SomeOtherConditionYouWantToCheck = False Then
        BadRecord = True
        ImportAndProcess = False
        Exit Function
    End If
    
    'and so on
    'bad conditions like isNumeric(rs!somefield) = false
    'or rs!someothefield > SomeArbitraryValue
    'You get the idea

    
    rs1.MoveNext
    
    
Loop

'MsgBox "The records were good"

'Now blow the records into their new home
rs1.MoveFirst
Do Until rs1.EOF = True
    Set rs2 = db.OpenRecordset("select * from tblPermanentHome where SomeField = " & SomeCondtionMaybe & ";", dbOpenDynaset, dbSeeChanges)
    'use this to check if some of your imported data is already in its permanent home due to partial import perhaps
    If rs2.RecordCount > 0 Then
        AlreadyCommitted(x) = rs1!JobID
        x = x + 1
    Else
        With rs2
            .AddNew
            !SomeField= rs1!SomeField
            !SomeOtherField = PerhapsSomeCondition
            'You get the idea
            !DateCommitted = Now() 'lets you know when you imported this particular record
            .Update
        End With

    End If
    rs2.Close ' close the permanent home table
    Set rs2 = Nothing 'you'll re-open it to check the next import item for existence
    'continually opening and closing a recordset with 0 or 1 record is faster than searching a larger set you keep open
    rs1.MoveNext
Loop

If x <> 0 Then
    Dim y As Integer
    Dim MsgString As String
        MsgString = "The following records already have committed entries:" & vbCrLf
    For y = 0 To x
        MsgString = MsgString & DLookup("SomeIndicativeField", "tblPermanentHome", "SomeIndicativeField = " & AlreadyCommitted(y)) & vbCrLf
        If Len(MsgString) > 300 Then
            MsgBox MsgString
            MsgString = "These too:" & vbCrLf
        End If
    Next y
    
    MsgBox MsgString
End If
    
ImportAndProcess = True



End Function

Open in new window

0
 

Author Comment

by:epuglise
ID: 35113387
re the textimport function...

hmmm... it also appears that the file i'm importing is not a .csv but is .xls  ... which acImport do i use? acImportDelim or acImportFixed... I'm getting a "You can't import this file" error when i run the code; however, when i go outside of the code and use my saved import directly, it works fine.

0
 

Author Comment

by:epuglise
ID: 35113389
oh! it didn't refresh and i didn't see you posted code! ok hang on let me try this.... LOL
0
 

Author Comment

by:epuglise
ID: 35113401
Ok this is fantastic code! I am learning a lot just looking at it. I may not be back to ee till Monday but I always assign points, so don't worry if you don't hear back for a bit.  This is going to take me a bit of time to fully digest.

Thanks so much for the help!!!  :)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113402
How to import a binary Excel spreadsheet needs to be a separate question.

This one was how to load a CSV.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113407
I guess if the above code works, it will be OK...  ;)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35113505
Excel opens CSV files, but I do this with both Excel CSV files and notepad CSV files.
The key is getting the link table right.
With Access 2003 that can be quite the bugger because it can assign unwanted data types to columns.
So, I created dummy data in the file I link, makes sure Access gets the data right and finalize the link.

Since Access DOESN'T revisit the datatypes when the underlying file changes, this works good :)

I was composing my first message when your first back-and-forth was ongoing

Nick67
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113521
I know Excel will open csv files.  The issue here is the difference between loading a text based CSV file into and Access table  and a Binary Excel file.

Different questions.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35113623
@slightlywv
Methods involving saved imports, I don't know from.
Access 2003 is my weapon of choice --which doesn't have them.

I know my code will work with either an Excel CSV or a Notepad CSV.
Set up the appropriate link table and the rest is golden.

TransferText vs TransferSpreadsheet, I don't know.
If the OP wants to go that way, I have nothing to offer.

I like the granularity working with recordset gives me in ensuring stuff is clean before it goes in, and not duplicated if a partial import does occur.
But that's me
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113861
>>it also appears that the file i'm importing is not a .csv but is .xls

I might be missing something but to me one is a flat txt file and one is a binary proprietary file format.

I apologize if an Excel CSV is not a form of text file.  That is a naming convention I an not familiar with.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35113881
No biggie.
If you open notepad, enter 1,1,1,1,1,1,1,1,1,1,1 and close and save it as test.csv, you'll find that you can open, read and edit with Excel
Conversely, if you open Excel and enter 1 in the first ten columns and then save it as test1.csv, you'll find that you can open, read and edit it with notepad.

CSV format is not a binary proprietary file format.
Note that if you RENAME a .xls file to .csv that this is not the same thing as a Save As operation.

Try it!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113892
So there is really no difference between an Excel csv and notepad csv.

Both are flat text files.  That is what I've been saying.

What are you saying?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113984
Nick67,

I don't know what your objection/argument is.

Sure Excel automatically knows how to open/save a TEXT file in CSV format but that isn't the problem here.

>>TransferText vs TransferSpreadsheet, I don't know.

The docs on TransferSpreadsheet:
http://msdn.microsoft.com/en-us/library/bb214134(v=office.12).aspx

It appears the difference is night and day.  One handles a TEXT file in CSV format.  The other will process a Excel spreadsheet saved as a Workbook in XLS format.  

See the parameter:  SpreadsheetType
http://msdn.microsoft.com/en-us/library/bb225982(v=office.12).aspx

The question as asked is how to load a CSV file (Flat Text).  The TransferText call will do that.

Back in http:#a35113387 they posted "it also appears that the file i'm importing is not a .csv but is .xls"

To me this is a Binary Excel Workbook file NOT a flat TEXT CSV file.

They are different.

I don't want to hijack this question any more with our debate.  If you wish to continue this discussion, I will be more than happy to open a Private Discussion over in Commuinty Support for us.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35114233
epuglise,

If you are doing an xls import that is a whole different animal from importing a flat CSV file.

I would link the xls as a table object in Access. It then would be read from InRS and write to OutRS (native Access table) then delete the InRS link.

Another thing to avoid is using import/export specs for flat files. I have seen them lost time and again. I even wrote an article about exports.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35114243
@Nick67

This is an Access2007 environment as stated in http:#35113290
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35114277
@epuglise

How do you determine whether an imported file meets the import spec?

I think that this might be something that will need to be verified before the entire file is actually imported.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35114300
@slightwv

The code I work with could care less whether the needed import file is .csv, .xls, or even .mdb
Heck, it could conceivably work with a Paradox file!
As long as you can create a link table to it, and replace it with a file copy operation, my code works.

I am NOT pursuing a TransferSpreadsheet or TransferText angle, so file type -- to me -- is irrelevant.
You have offered a different plan of attack, where that does matter.

In some ways, the posting at http://www.utteraccess.com/forum/Call-saved-import-Access-t1951727.html gives me the impression MS simply fully automated the tack that my code tackles piecemeal.

@epuglise was looking for some error handling, which is something my code offers.
No Biggie.  Hopefully no one gets confused.

Nick67

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35114318
The code I posted also doesn't care about version.
It presently works in Access 2003, 2007 and 2010 which are all present in my environment.

It's in the original poster's court now :)

Nick67
0
 

Author Comment

by:epuglise
ID: 35131547
Holy cow. I step away from the computer for the weekend... and... i have a lot of questions to answer!

@aikimark specifically and everyone in general: The file I'm importing is one I create from the tool, so while I probably _should_ do detailed review of the data, I'm going to be lazy on this one-- if someone tries to import a random file, they'll get an "import error" because the saved import will fail. :)

The file is exported originally from the tool as a comma demilited file, but I have it saved as a .xls so the reviewer can open the file and look at the information before importing it. So in my mind it's a comma delimited file because that's how it is created, even though i save it as a .xls.  Forgive me if I introduced that confusion.

Finally, I appreciate the responsiveness of slightwv because he responded while i was working on the problem and much of the code i ended up using came from the links he provided.  The code from Nick67 included further detail that enhanced my code.  I learned a lot from both info providers so I'm splitting the points and hope everyone is satisfied with that :)  I'm pretty bad at this stuff and I have a paid account so don't worry, I'll be posting plenty of (probably pretty easy) 500 pt questions :D

THANK YOU for passing along your knowledge!!!

e
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35132162
@epuglise

If you ask a follow-up question to this one, please use the ask a related question link.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now