Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Splitting up columns and then combining a directory

AshaCharis
7 - Meteor

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?

4 REPLIES 4
AshaCharis
7 - Meteor
 
JessieC
Alteryx
Alteryx

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

clipboard_image_0.png

AshaCharis
7 - Meteor

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!

JessieC
Alteryx
Alteryx

@AshaCharis ,

 

Nice!

 

Since you're skipping the first two rows, you can configure Input Data options to skip rows by entering a different value for Option 8: Start Data Import on Line:

clipboard_image_0.png

Labels