topic RE: Importing data to collection from .csv using OLEDB in Product Forum

Importing data to collection from .csv using OLEDB

mroemer — Wed, 12 Apr 2023 12:37:00 GMT

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
------------------------------

RE: Importing data to collection from .csv using OLEDB

Neel1 — Wed, 12 Apr 2023 13:18:00 GMT

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
------------------------------

RE: Importing data to collection from .csv using OLEDB

mroemer — Wed, 12 Apr 2023 13:39:00 GMT

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

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

RE: Importing data to collection from .csv using OLEDB

Neel1 — Wed, 12 Apr 2023 13:45:00 GMT

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-4699-bf49-d1142daeec53

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

RE: Importing data to collection from .csv using OLEDB

michaeloneil — Wed, 12 Apr 2023 14:30:00 GMT

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
------------------------------

RE: Importing data to collection from .csv using OLEDB

mroemer — Thu, 13 Apr 2023 08:52:00 GMT

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
------------------------------