Read .csv files from local directory, modify them, bulk upload to SQL

I'm running 'x' number of APIs that deliver .csv flat files. I put them in their own directory.

I name them according to the ID# that served as the identifier in the API call that downloaded the file. For example 1234-234-234-5432.csv.

That identifier is not in the downloaded file. I want to add that identifier to the file as a column, so, an extra field is added to all rows. Then publish the file to SQL Server. All from my local machine. That identifier serves as the key field to the rows in the file.

Trying to do this on the fly is very problematic, I have to use dynamic rename and the append fields tools, and they do not get along well. When I do the append fields, I'm pulling in "only" the ID - no other fields. And then, not all the columns of my file import. I can avoid hassles just by going from API call straight to the download tool and save it locally.

But, I have to put these files on SQL Server with that ID as I explained.

So in theory, I'd like to just download all the .csv files; then Insert into each file their ID, ( which is the name of the file ) as a new column, upload to SQL.

Thanks for any assistance,

Chris

Publish

Batch Macro

Accepted answers

Luke_C

Hi @Archaeopteryx

If you right click the yxmd and yxmc and open them in a text editor (notepad, etc), update the version data there to be 2020.3 and save. It should work fine after that!

All comments

Luke_C

Hi @Archaeopteryx

I get data in a similar way (transaction data but the transaction ID is the filename itself) and I accomplish this by outputting the file name as a field in the data. (Option 4 in the screenshot). This seems like a simple answer so apologies if I'm missing something due to the various components you mentioned.

Archaeopteryx

So, I am saving the file that way. I need to add that filename, "1234-234-2134-2345" as a column to the file. That value would be added to each row of the file. I would then want to resave, then publish to SQL. My quandary is adding that file's name as a value in a column.

Luke_C

Hi @Archaeopteryx

Inputting the files with that option selected will result in a column being added with the file name repeating for each row of data (you may need to parse out the .csv depending on your needs). You could then re-output to CSV. In the output tool you can define the name of the CSV based on that same filename field. Does this help? If you have a sample file it might help demonstrate further.

Archaeopteryx

This kinda works. But not all files get transformed. See screen shots. Perhaps the configuration of the input tool is not quite right. The endeavor is to take any number of files that reside in their own special directory just for them - - as it cannot be known in advance how many - - and open each one, one at a time, and resave it with its own file name as a field, [FILENAME], or column. The value of the column will be the same for every record in that file, the file's very own name - - i.e. "1234-432-543-0987".

I may get three files transformed, I may get one file transformed, I may get none at all - just zip - ping - done and nada. Or one or two. See Screen Shots.

Header Row: [FILENAME]

Row 1: 1234-432-543-0987

Row 2: 1234-432-543-0987

Row 3: 1234-432-543-0987

I've tried using the Throttle Tool set at 60,000 records per minute - it really does not help. The Crew Macro Wait A Second hangs at 50% even if you put in a pause of 1 second.

So we know that what I want to do is doable, creepy part is - the process is choosy and will not transform all files.

Is it perhaps the way I'm introducing the files in the Input Tool? maybe \path\*.csv is not right?

So, any assistance is appreciated.

Chris

1InputScreenShot.png

1OutputScreenShot.png

1StartingWithFiles.png

1EndingWithFiles.png

Luke_C

Got it, I think a batch macro may be best since you're working with csv files with different schemas. This would work using a directory tool (update to your folder location) to read in the list of csv files and pass them one by one through the macro. The macro will read in the data, add the column with filename, and write the data back to the csv file. Take a look at the attached. You will need to update based on your locations and settings.

New Workflow26.yxzp

Archaeopteryx

Luke,

thank you for the assistance and workflow sample. I am running version 2020.3 since our server is also running 2020.3. We won't be updating to a newer version until later this year or early next year. can this workflow be made to run in 2020.3 ?

Chris

Luke_C

Hi @Archaeopteryx

If you right click the yxmd and yxmc and open them in a text editor (notepad, etc), update the version data there to be 2020.3 and save. It should work fine after that!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703