Splitting up columns and then combining a directory

AshaCharis

Hi,

I am new to Alteryx. 🙂

I am currently having trouble combining a directory and also splitting all the excel files into columns.

What I want it to do is:

- Skip the first two rows in each excel file

- Combine all of the excel sheets together

- Split the text to columns based on the first row as headers

Does anyone have any experience in this?

I have attach a picture of what I have done so far.

the error it is giving me is "too many fields in record #1", which I am assuming means I need to split the columns before I combine the files together maybe?

screenshot_of_helpme.PNG

Batch Macro

Dynamic Processing

Accepted answers

AshaCharis

Hi @JessieC !

I figured out my problem. The problem was occurring due to varying delimiters in the data. The first two rows of the csv are a header that is pipe "|" delimited and the body was semicolon ; delimited. By configuring the input tool of the macro to use the carrot ^ as the delimiter (just a random symbol that should never show up in the data) the input tool was able to read in the data as one column. Then the sample tool was applied to skip the first two rows. This is the output of the batch macro. The text was then split to columns using the ; delimiter and all is well!

I have attached a sample data to show what it looked like!

Sample_Data.xlsx

All comments

AshaCharis

JessieC

@AshaCharis ,

Can you share what the data looks like? Is it a .csv? Usually if there is an extra delimiter, it will cause the data to parse incorrectly and give that error.

You can read in the file with no delimiter \0 and parse with Text to Columns after.

Modify the Input Data Tool Options to change the Delimiter. You may adjust Option 9 depending on your data set.

AshaCharis

Hi @JessieC !

I have attached a sample data to show what it looked like!

Sample_Data.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703