Drop lines on CSV import into MS Access 2010 1

[greyoxide]

To preface I know nothing about VBA, my primary position is a web developer using Adobe ColdFusion and some PHP. This gives me a little confidence with VBA, but I am still a complete noob and know pretty much nothing

I am trying to use MS Access 2010 to create a tool that will import a monthly CSV file and spit out a bunch of individual reports which can be given to clients. I have a form that will browse to a CSV file and append it to my stats table. The CSV file has 9 rows of report title before the column headers, then two lines of column totals under the header. These nine rows of report title throw off the import, and cause my macro to crash.

Is there a way to use VBA to ignore these first rows in Access 2010? Also if this were possible, could it also ignore the two rows under the column headers as well(rows 13 and 14)? The path to the file is stored in a form field [Forms]![Import]![Path]. Any help would be very appreciated.

 

[SkipVought]

The best approach might be to recreate the report as a proper TABLE in Excel on a separate sheet, where YOU decide what headings go into row 1, and then the DATA following.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I typically import the entire file into a temporary table with a working table structure. Then delete un-needed records and append the remainder to permanent tables.

Duane
Hook'D on Access
MS Access MVP

 

[greyoxide]

I could pretty easily just edit the CSV that I get monthly. The end users though will get frustrated with that and likely stop using the app.

I found a page where someone asked pretty much the same question but due to my inability with compiled code I was not able to make it work.

http://www.tek-tips.com/viewthread.cfm?qid=1562271

I guess a significant factor behind the failure is that I do not know how to call the path form variable in VBA.

 

[dhookom]

What do you mean by "call the path form variable"? What code did you try? What error message did you get?

Did you attempt to pull the file into a staging table and then process it in Access?

Duane
Hook'D on Access
MS Access MVP

 

[xlbo]

Agree with Duane - generally accepted practice for importing "badly" formatted files into databases is to import 1st into a staging table - 1 field only. then delete your extraneous rows. From that point you have 2 options

1: dump the staging table back out to a flat file andre-consume in the "proper" format into your final table
2: Cod from the staging table to the final table using code to pick apart the delimiters used for the file and create multiple fields from 1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Agree with Duane - generally accepted practice for importing "badly" formatted files into databases is to import 1st into a staging table - 1 field only. then delete your extraneous rows. From that point you have 2 options

1: dump the staging table back out to a flat file andre-consume in the "proper" format into your final table
2: Code from the staging table to the final table using code to pick apart the delimiters used for the file and create multiple fields from 1e

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[greyoxide]

Im sorry for the late reply it was a pretty hectic end of the week last week.

So the path variable is the field on my import form that stores the path to the file to be imported. The import form is called "import" and the text field on that form that stores the path is called "path" ( Forms![import]![path] ).

Im not familiar with concept behind a staging table. I assume that this is a temporary table that is generated to be imported into then dropped once the the import is complete? Automating this process is pretty far over my head. Scripting this out in VBA would be more or less impossible with my current understanding.

Can this process be encapsulated within a macro?

Thanks again for the wealth of replies.

 

[sxschech]

I think that vba code should work to remove the unwanted lines. I noticed in the code from the thread you provided, it is looking using fso. Did you set a reference to Microsoft Scripting Runtime in your vba? In the vba window click on tools references and add a tick to Microsoft Scripting Runtime.

For your path did you set up something like this? If your code is behind the form (meaning you have an action on the form rather than a separate module in the modules section), then you could use me as shown below. You mention that the form shows the path, where does the file name come from, is it on the form too, or does user enter it? If the path doesn't have the final backslash, you would need to add that in the code. I put the backslash in the filename, but if you already account for that, remove it.

...
...
    Dim stPath As String
    Dim stFileName As String

stPath = me.path
stFileName = "\import.csv"

...
...

set outFile = FSO.CreateTextFile(stPath & stFileName)
...
...

Here is another thread about removing lines.

http://www.tek-tips.com/viewthread.cfm?qid=783118

 

[xlbo]

ok going down Skip's route of opening in excel and sorting it all out (to save messing about with fso and line breaks), the following should work quite nicely:

Sub Fix_CSV()

Dim filePath As String

filePath = "C:\testFile.csv"

Dim oXL As Object

     
     '   Create a new Excel instance
    Set oXL = CreateObject("Excel.Application")
    
    Dim wb As Object
    
     '   Full path of excel file to open
    On Error GoTo ErrHandle
       
     '   Open it
    With oXL
        '.Visible = True
        Set wb = .Workbooks.Open(filePath)
        
        With wb.Sheets(1) 'only 1 sheet in a csv
        
            .Rows("13:14").EntireRow.Delete
            .Rows("1:9").EntireRow.Delete
        
        End With
        
        wb.Close (True) ' closes and saves
        
        .Quit 'exit xlapp
        
    End With
     
    Set oXL = Nothing ' remove object reference
    
    Exit Sub
    
ErrHandle:
    oXL.Visible = True
    MsgBox Err.Description

    
End Sub

Just change

filePath = "C:\testFile.csv"

to

filePath = [Forms]![Import]![Path]

not used Access for a long time and can't remember how to set a variable in code based on a textbox value but you should be able to google that pretty easily

the rest is pretty basic and should work nicely. The csv will be opened in excel (invisibly), rows deleted and then saved back to its original location. After that you can run your import. If anything fais, excel will be made visible and th error message shown


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[greyoxide]

Wow Geoff, your script worked beautifully! It executed quietly and next thing I know my CSV started with the header at the top. Thank you very much!

One more question, again I'm sorry for the noobishness of this question. This code needs to be in a macro that actually does the import. How do I run this from the macro? I've tried the "run code" function but that does not work. Also do I have to insert a stop in the macro to make it wait until your script finishes?

thanks again.

 

[xlbo]

As I mentioned - not used Access for years but this seems to be the way to go:

http://www.mrexcel.com/forum/showthread.php?304356-Running-VBA-code-with-a-Microsoft-Access-macro

rename the sub to a function and use the RunCode macro command

you would simply place this as the 1st step of your macro

the import steps would then happen after so no need to pause or anything

If there is an issue with the macro running on before the code finishes I do seem to recall there's a property you can set somehwere in the macro to force it to wait until each step is omplete before moving on

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question