Forum Discussion
change positions
I have the following csv file with 2 lines but it could contain many lines of information being the first line alway the same header
and then each consecutive line has purchase order details:
file1.csv
Line 1 SalesOrderNumber,LineItem,OrderNumber,Description,PartNumber,Revision,UnitOfMeasure,Location,OrderType,InventoryPlanning,PlannerCode,OrderDate,RequestDate,PromiseDate,Quantity,RecommendedOrderQty,Priority,Expedite,Vendor,VendorId,UnitCost,ExtendedCost,VarianceCost,Customer,Source,Notes,VarianceNotes,UDF1,UDF2,UDF3,UDF4,UDF5,UDF6,UDF7,UDF8,PercOfBuffer,Profile,PartType,MaterialType,Order,Variance,StartDate,Late,Status,OrderId,DueDate,LinkCode,CreatedByUser
Line 2 actual data like described on line 1
Line 3 actual data like described on line 1
Line n actual data like described on line 1
I need to read this CSV delete the header on line 1 and leave it like this with the data:
file2.csv
Line 1 Location,,OrderDate,PlannerCode,PartNumber,,Location,UnitOfMeasure,Quantity,PromiseDate
.... repeat for as many lines as there are after line 2, then save the file as csv on a directory and delete file1.csv
The OrderDate and PromiseDate are on file.csv on the format 10/30/2017 but on file2.csv they need to be 20171030
Please help, thank you.
Hello,
you can use Power Query to do that. Power Query is a free add-in from Microsoft for Excel 2010 and 2013 and built into Excel 2016 as Get and Transform.
Create a new query from a file, navigate to the CSV file and open it. Remove the columns you don't need, reorder the columns as you need them and save the query to the worksheet. Save the file as an Excel file.
Then use Save As to save the sheet with the query result as a CSV file.
When the original CSV file changes, you can open the saved Excel file, refresh the Power Query and save the query result as a CSV file again.
7 Replies
Hello,
you can use Power Query to do that. Power Query is a free add-in from Microsoft for Excel 2010 and 2013 and built into Excel 2016 as Get and Transform.
Create a new query from a file, navigate to the CSV file and open it. Remove the columns you don't need, reorder the columns as you need them and save the query to the worksheet. Save the file as an Excel file.
Then use Save As to save the sheet with the query result as a CSV file.
When the original CSV file changes, you can open the saved Excel file, refresh the Power Query and save the query result as a CSV file again.
And my last problem is that after I run the query I need to delete the data from the source, can I do that?
Hello,
it may take a little while to get the hang of everything that can be done with Power Query.
You can transform values by creating custom columns with functions that manipulate the original data. You may need to use this technique to create a date that Power Query recognizes. Also, there is a setting to specify the locale of the original date.
You can open all files in a specific folder and combine them
You can use VBA in a workbook. Use Power Query to load the data, then use VBA to delete the data source after the Power Query load was successful.
It would be best if you started a new question about a specific problem, so that each issue can be addressed separately.
And also, the problem I have is that the CSV source is not one file, is one everytime someone runs a function so there could be multiple CSV files, and I need to automate this so that no ones opens it from a folder manually.....
Great thank you. I am trying it out. I dont see a way though to change the date format....
