0

I followed Export table from database to csv file and used this command line:

sqlcmd -S . -d DataBase -E -s, -W -u -Q "SELECT * FROM Table" > C:/Table.csv

But the file's second like is being used to separate first line's column names to third line's data:

-----------,------,-------,--------,------,------,------,-------,--------,---------,--------,---------,---------,----------

I aim to import this file on Excel PowerPivot, and can't figure a way to tell it that second line must be ignored.

Is there a way for MSSQL not print this separator line, or at least make PowerPivot ignore it?

Update, I was able to export data using the command

sqlcmd -S . -d APP_BI -E -s, -W -u -Q "SELECT * FROM DB.dbo.Table" | findstr /V /C:"-" /B > C:\Table.csv

executed from cmd.exe. But when I add it to to cmd file, or execute it directly from MSSQL Agent, I get error message Sqlcmd: '| findstr': Unexpected argument. Enter '-?' for help.. So, pipe doesn't work inside a cmd file or from MSSQL Agent, they think it's a parameter for sqlcmd.

This leads me to begining, I need a way to generate the csv without that line.

Community
  • 1
  • 1
Hikari
  • 3,797
  • 12
  • 47
  • 77
  • Have a look into URL in answer to question you've posted: https://www.excel-sql-server.com/sql-server-export-to-excel-using-bcp-sqlcmd-csv.htm and read it until the line *"...where findstr /V /C:"-" /B removes strings like "--,-----,--------,--------"."* – Andrey Korneyev Feb 08 '17 at 15:09
  • Why are you exporting the data to reimport it to Excel instead of connecting from Excel to the database directly? Or exporting to an Excel file directly with SSIS? – Panagiotis Kanavos Feb 08 '17 at 15:09
  • Thanks Andy it worked! if you post it as answer I'll mark it – Hikari Feb 08 '17 at 15:12
  • I wanna provide this xlsx file to people that don't have permission to access the DB. I'm gonna export data to csv files, anyone willing to will be able to ETL them anywhere they want, and also use the xlsx with configured PowerPivot to analyze data and update it from csv. – Hikari Feb 08 '17 at 15:13
  • @Hikari I don't think it worth to be an answer since all efficient information already in answer to question you've mentioned. So both my "answer" and your question are duplicates of that thread and contains almost nothing new. – Andrey Korneyev Feb 08 '17 at 15:21
  • Unfortunately it didn't work. I'm gonna update the question. – Hikari Feb 08 '17 at 17:32

0 Answers0