none
power query multiple csv import RRS feed

  • Question

  • Hello

    Im using excel 2013 I have some csv files that are generated from a monitoring software

     I am trying to set up a process in power query to extract this data from each csv file and add them to a table.

    The processes that I have to do is open the first csv swap the columns (4 columns in each csv) to rows, remove the two last rows, make the first-row header (only in the first csv of the batch). Then on the rest csv file swap columns, remove fist and last two rows and add them to the table. Is this possible to do that with power query

    I have an example of the csv files

    Instrument Name,tarkII

    Model Number,8530

    Serial Number,8530172114

    Firmware Version,3.7

    Calibration Date,30/11/2018

    Test Name,MANUAL_003

    Test Start Time,10:53:20 AM

    Test Start Date,14/03/2019

    Test Length [D:H:M],0:0:3

    Test Interval [M:S],0:10

    Mass Average [mg/m3],0.117

    Mass Minimum [mg/m3],0.107

    Mass Maximum [mg/m3],0.129

    Mass TWA [mg/m3],0.000

    Photometric User Cal,1.00

    Flow User Cal,0.00

    Errors,

    Number of Samples,18

    Elapsed Time [s],Mass [mg/m3],Alarms,Errors

    10,0.108,,

    20,0.114,,

    30,0.109,,

    40,0.119,,

    50,0.111,,

    60,0.107,,

    70,0.118,,

    80,0.113,,

    90,0.118,,

    100,0.114,,

    110,0.112,,

    120,0.114,,

    130,0.129,,

    140,0.119,,

    150,0.123,,

    160,0.127,,

    170,0.128,,

    180,0.127,,

    In any case we can skip the header step

    Thank you I advance

    Friday, March 29, 2019 7:11 AM

Answers

All replies

  • Is the sample data you pasted above from one csv file, or more than one? Also, what does the expected output look like?

    Ehren

    Monday, April 1, 2019 10:58 PM
    Owner
  • hello Ehren thanks for replay

    the above is one CSV i have multiple files like this that have sort of info. actualy its csv is a report of a experiment.

    i need to take from each csv some of the data and inprt them to the excel.

    as you can see the file has four columns, i need to take only the second column from each file and save it as a row in excel 

    xazirhs

    Wednesday, April 3, 2019 5:47 AM
  • Ok. Have you tried importing the CSV, removing all columns but the second one, and transposing the table? You should be able to do this to each file and then Append them together.

    Ehren

    Wednesday, April 3, 2019 4:50 PM
    Owner