In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Merge multiple CSV into single excel

Yash_Nagda
5 - Atom

I have nearly 2000 csv files. each of them contains approx 700 rows, but I only need first 18 rows of each csv 
the first 18 rows contain data like :
Name,Adam
Age,17
City,Amsterdam
.

.

.

So On...
I need an excel which has there 18 columns as headers Like Name, Age,City...etc. and the excel file should contain 2000 rows i.e. the info from each file 

I am adding some 3 or 4 demo files for reference

5 REPLIES 5
DataNath
17 - Castor
17 - Castor

@Yash_Nagda is this what you're looking for?

 

DataNath_0-1665736384065.png

 

In terms of the steps taken to achieve this, I:

1) Placed all CSVs into a directory and used a wildcard to bring them in (the *.csv part of the Input Data configuration):

 

DataNath_1-1665736437153.png

 

2) Used 2 sample tools to a) skip the first row of each file - containing the version # and b) isolate the first 18 rows of each file:

 

DataNath_2-1665736478149.png

 

3) Split the CSVs into their 3 columns using Text-to-Columns:

 

DataNath_3-1665736499384.png

 

4) Cross-tabbed the data, using the newly-split fields as headers/values and grouping by the filename to stop them becoming aggregated into a single row:

 

DataNath_4-1665736533729.png

 

Hope this helps! Please let us know if you need further assistance.

Yash_Nagda
5 - Atom

@DataNath This is exactly what I needed ....I wantede to ask what will be the limit of number of csv files when I change the input to *.csv ?

DataNath
17 - Castor
17 - Castor

Great to hear @Yash_Nagda! As far as the number of CSVs you can bring in with the use of a wildcard, I'm not aware of there being one but let me know if you run into any issues. There's a helpful blog here on inputting multiple files if you want a bit more info: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

flying008
15 - Aurora

Hi, @Yash_Nagda 

 

See the below gif :

 

录制_2022_10_14_16_58_24_580.gif

 

 

 

Emmanuel_G
13 - Pulsar

@Yash_Nagda 

 

Find in attachement an easy way of doing that dynamically with dynamic input tool.

 

Emmanuel_G_0-1665738259040.png

 

Labels
Top Solution Authors