asked on

Automating CSV file import using Excel 2003

Dear All,

I ma having difficulty with the importing of CSV files, as there are so so many of them for me to do at any one time.

I was wondering if anyone can help me speed up the process through some sort of macro automation?

I have attached a text file (renamed from CSV)

Now what I do is go to data > import Data, then I import the csv file...

The only bit I really need to import is the Website Urls part, and the Keywords only need to go in the once as they will be all the same...

So at the end of my importing I will have one long excel page with the keywords at the top, then under them all the Website URLS from the other csv files...

The attached document is a typical file, and even though there happens to be only one keyword there are typically far more...

Can anyone help me with the automation process of the importing of all these CSV files?
Thanks Experts,

Kevin

Copy-of-1.txt

Jorge Paulino

Hi Kevin,

If you replace the "," with ";2 will open fine!

Is it ok for you ?

oconnork00

ASKER

Hi Ipaulino,

In the CSV file you say to replce "," with ";2 but how will this speed up the automation process?

Thanks,

Kevin

Jorge Paulino

If you save the file to disk then you can add a button to select the file and do that work

or

After open the file (on double click ) automate the text to columns.

What do you perfer

oconnork00

ASKER

What I think would work best is to have a custom button in Excel that would let me choose the folder that has the csv files in...

Then a macro would run and take the first set of keywords from the first csv file, and the Website URLs, then the Website URLs from all the rest...

Does that sound ok?

Would you be able to offer help with the macro to do this?

Many thanks

Kevin

oconnork00

ASKER

I looked into a macro to import just the first file in the directory, and it worked well. Unfortunately I dont know how to loop this macro to do the remaining files in the folder...

Any advice?
Thanks,
Kevin

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/02/2008 by
'
 
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\RenamedFolder\3_Auto0001.csv", Destination:=Range("A1"))
        .Name = "3_Auto0001"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A12").Select
End Sub

Open in new window

RobSampson

Hi Kevin, this macro will go through all files in the folder specified, but we just need to go back through the code and delete the Keyword rows....

Regards,

Rob.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/02/2008 by
'
 
'
    strFolder = "C:\Temp\Scripts\Destination\"
    If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
    strFile = Dir(strFolder)
    Do While strFile <> ""
        If LCase(strFile) <> LCase(ActiveWorkbook.Name) Then
            'MsgBox strFolder & strFile
            intLastRow = Cells(65536, "A").End(xlUp).Row
            If intLastRow > 1 Then intLastRow = intLastRow + 2
            With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;" & strFolder & strFile, Destination:=Range("A" & intLastRow))
                .Name = Left(strFile, InStr(strFile, ".") - 1)
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 850
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With
            Range("A12").Select
        End If
        strFile = Dir()
    Loop
End Sub

Open in new window

oconnork00

ASKER

Hi Rob,

So just so I'm sure, should I copy and paste this code into a new module, rename the file path to my file path?

Does this take into account CSV files?

RobSampson

The code can go into any Module in an Excel file, and you just change this
strFolder = "C:\Temp\Scripts\Destination\"

to the source folder that has your CSV files in it. It will actually try to open every file in that folder, whether they are CSV or not, so that includes CSV and TXT files. This can, of course, be rectified if you need it that way.

Regards,

Rob.

oconnork00

ASKER

Update - ok that actually works almost exactly what I wanted... Thanks Rob.

The only problem I have is that the Keywords part with all the keywords are getting imported from each csv file.

Each CSV file contains the same list of Keywords. Ideally only the first set of keywords would get imported from the Macro, and then everything else would import too.

I have Website URL as the header for all the other data - is there something else I can do in Excel to filter this?

I have attached an example of the Excel Spreadsheet which I hope explains a little better what I am saying.

Please note that keywords and Website URLs are in the same row... You could essentially ignore everything else to the right.

Rob - you are a great coder and communicator. Thanks for everything.
spreadsheet.xls

ASKER CERTIFIED SOLUTION

RobSampson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

oconnork00

ASKER

EXCELLENT!

Rob thank you very much for this piece of work. I am truly greatful.

Thank you.
Kevin

oconnork00

ASKER

Hi Rob,

I was having problems with this earlier, so opened a related question here: https://www.experts-exchange.com/questions/24333984/Excel-Macro-Run-Time-1004.html

if you have the time I'd appreciate it if you could take a look!

Thanks Rob

Kevin