community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
SOLVED

Splitting up columns and then combining a directory

Highlighted

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?

 
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

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!

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