Importing data to collection from .csv using OLEDB

[MichaelRømer]

I've browsed the forums and found a lot of help getting started using OLEDB. However, I'm having an issue when importing a .csv file into a collection.

I can manage to import the data, but it's imported into a single column separated with ";" even though the file is with columnheaders.

File:

This is my connection string: "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & [Path2] & ";Extended Properties = ""text;HDR=Yes;FMT = Delimited(;);Persist Security Info = False"""

The file contains over 100k lines, so my goal is using a "Get collection" to only import the relevant part of the csv file, using an expression looking something like this:

"SELECT [cprnr],[dato] FROM ["&[FILE]&"] WHERE [cprnr] = '"&[CPR]&"'"

However using this, I get the error "Internal : Could not execute code stage because exception thrown by code stage: No value given for one or more required parameters." 

Does anyone have some insight towards a solution? :)

------------------------------
Michael Rømer
------------------------------

[Neel1]

hello Michael Rømer

alternatively ,There is an action in Utility - File Management VBO called as Get CSV Text as Collection - Chunk. You can use this read data from CSV in chunk.

------------------------------
Neeraj Kumar
Technical Architect
------------------------------

[MichaelRømer]

Thank you, Neeraj. I will try it out and report back :)

------------------------------
Michael Rømer
------------------------------

[Neel1]

hi Michael Rømer  - i have mentioned one thread for this feature which can help you checking this action out.

https://community.blueprism.com/communities/community-home/digestviewer/viewthread?MessageKey=16adfb54-82e0-4699-bf49-d1142daeec53&CommunityKey=1e516cfe-4d1f-4de9-a9eb-58d15bf38c81#bm16adfb54-82e0-469...

------------------------------
Neeraj Kumar
Technical Architect
------------------------------

[michaeloneil]

Hi Michael

I think your sql script may be a little off, the column field headers dont need to be in  brackets and the criteria only needs to be in single quotes. Also it looks like File is being used where the sheet name should be, the worksheet name should be encased in brackets and with a $ after the name e.g. "SELECT cprnr,dato FROM [Sheet1$] WHERE cprnr = '123'"

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[MichaelRømer · Accepted solution]

I ended up with a solution querying into the csv file with the expression below, which yelded the wished results. I can then split the collection as needed afterwards. Thank you for your replies.

"SELECT * FROM ["&[FIlENAME]&"] WHERE [2;cprnr;aarsag;dato;skolekode;] LIKE ('%"&[Cpr2]&"%') AND [2;cprnr;aarsag;dato;skolekode;] LIKE ('%"&[Dato]&"%')"

------------------------------
Michael Rømer
------------------------------