Transpose many columns and rows to database structure

Hello All,

I receive a simple CSV file with Ship-to, Supplier-Code, Part-Code all down rows (can be hundreds of rows)...then in the corresponding columns have the Demand, with the Calendar weeks (rolling 26 weeks) in row 1.

I need the file to be transposed so it can be uploaded into a basic database, which has the columns: Ship-to, Supplier-Code, Part-Code, CW (calendar week), and QTY (demand).

Lots of information out there on basic transposing, but this is a little harder and with my limited VBA this is beyond me.

For simplicity, I think, I am trying to leave the Original data in sheet "Orig" and transpose it into "New". But I am open to any idea as long as it transposes correctly. I added for an example "Example of how it should look" sheet....this is a set of some of the data on how I would like it in the end so I can transfer the data into the database.

Attached is my example file, which of course has been limited in size.

Any ideas?

Thanks in advance!

Regards,
Matt

This will read the data directly from CSV file, if the structure is always the same.

Please Login or Register  to view this content.

Hello Jindon, Cooool!

So it works great on my test file (with actual data and with actual structure), then when I grab another file (with the same structure) I get this error:

screenshot.142.jpg


screenshot.143.jpg


What am I doing wrong?

The approach to just select the CSV is brilliant and the structure for all the files I have grabbed is the same.

On further testing I found that when I open the CSV file and then save as "excel csv" it works...could the pure CSV format from the direct download be causing it issues?

Many thanks!!

Matt

Then I need to see the CVF file.

Most probably you have blank row(s) before the heading...

Hi Jindon,

Hmmm, I am not sure how I can share without disclosing the confidential (however unimportant:-) information in the file. Looking at the file in NotePad I don't see a leading space or anything, just consecutive data separated by "," with no spaces.

Is there a way for me to see and tell you?

I can tell you that in the column header fields it has a "_" instead of a "-" which I have in my example. Does that make a difference?

If there is no easy way to solve, I can live with opening and saving as an excel based CSV file...because your code works brilliantly when saved like that.

Thanks,
Matt

OK

Can you just change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and see if it works.

Unfortunately, exact same error.

Then I need you to do a step debug.

While you are in VBE;
1) go to [View] - [LocalWindow] then you will see the new pane below the code pane.
2) click somewhere on the code and hit F8
3) as you hit F8, it executes one line and you will see all the variables in the LocalWindow.
4) Check "myTitle", if it loads heading. You can expand it if it has "+" sign.

Hi Jindon,

Like this:

screenshot.144.jpg

Thanks,
Matt

I see it is not splitting, so it uses other character.

This will read the data from "Orig" sheet and dump the result in "New" sheet.

Please Login or Register  to view this content.

And just try change vbCrLF to vbCr or vbLf for the first code.

Hi Jindon,

the "orig" to "new" code works like a champ, thanks!

So I tried the vbCrLF to vbCr same error.
I then tried vbCrLF to vbLF and it made it through the code, imported the original CSV. But...it added an additional row after each loop with columns A:D data, 27 instead of 26...CW and QTY both were correct at 26 rows of data....does that make sense?

Thanks!!
Matt

That means the file might have more consecutive columns (comma) at the end in a row...
Try this one then

Please Login or Register  to view this content.

Yeah, you've just copied before I edited the post.

Anyway thanks for the rep and marking "Solved".