CODEWITHSUNDEEP
vbaexcelmacros
DJohnson1990
DJohnson1990

Reputation: 57

Copying data from CSV and outputting it into another

I would like to do three things: import data from one CSV if the values in column 2 and 3 meet a criteria, translate this data based on a mapping key and output this translated data into a new CSV.

Sub in_out()



dim file_path as string
file_path = Worksheets("Sheet1").Range("A1").Value
open file_path for input as #1
row_count = 0

sheets("Sheet1").UsedRange.ClearContents
Range("A1").Select

Do Until EOF(1)
    Line Input #1, Line_FromFile
    Line_Items = Split(Line_FromFile, ",")

    If Line_Item(2) = Worksheets("sheet1").Range("B2").Value And Line_Item(3) = Worksheets("sheet1").Range("C2").Value Then


    ActiveCell.Offset(row_count, 0).Value = Line_Items(1)
    ActiveCell.Offset(row_count, 1).Value = Line_Items(2)

    row_count = row_count +1
    Loop

    else
    row_count = row_count +1
    Loop

    end if

    row_count = row_count +1
    Loop




Close #1

End sub

Upvotes: 0

Views: 158

Answers (1)

Martin Dreher
Martin Dreher

Reputation: 1564

You could consider using SQL with an ADODB-Connection instead of VBA-only. With large CSVs, this should be considerably faster than looping each row.

An SQL-Statement like this one could do the trick.

SELECT * 
INTO [Text;DATABASE=L:\Out-Folder].tblOut.csv
FROM [Text;DATABASE=L:\In-Folder].tblIn.csv
WHERE column2 = criteria1 AND column2 = criteria2

Note that you need a schema.ini-File describing the structure of you CSV. Those are reasonably easy and fast to set-up.

You then could either

a) run said SQL-query from directly from MS Access as an MS-Access query

or

b) run the SQL in MS Access-VBA like so:

Dim sql As String
sql = "SELECT * INTO ... FROM ... WHERE ..."
CurrentDb.Execute sql, dbFailOnError
or

c) run it from Excel-VBA, if you have to use Excel. This would require you to set up an ADODB.Connection first, roughly like this:

Dim MyConn As ADODB.Connection
Dim MyCmd As ADODB.Command

With MyConn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open yourDB.accdb
End With

With MyCmd
    Set .ActiveConnection = MyConn
    .CommandText = strSQL
    .CommandType = adCmdText
    .Execute
End With

Set MyCmd = Nothing
Set MyConn = Nothing

General Remarks:

  • be sure to backup your original CSV during developing

  • Since you use the same filter-criteria for each row (Cells B2 and C2), adjusting the SQL is rather straight-forward and can be done by either reading the Cell-Contents like you did (in Excel) or, for example, using a UserForm (in Access).

  • Note that in c), I first connected to an ACCDB called yourDB.accdb, which obviously senseless in my code, since you can directly connect to the textfiles with the proper connection string. However, if you plan to do some Mapping, you could maintain your Mapping-Tables in said Access-Database and use them in your SQL (with a Join).

  • In addition, I personally like the fact that in this case any path-information is contained inside the SQL-Statement, even if it has a slightly slower performance.

EDIT: Naturally, if you would rather append data to the Output-CSV (rather than replace it), you could use an INSERT-Statement like this:

INSERT INTO [Text;DATABASE=L:\Out-Folder].tblOut.csv
SELECT *
FROM [Text;DATABASE=L:\In-Folder].tblIn.csv
WHERE column2 = criteria1 AND column2 = criteria2

Upvotes: 3

Related Questions