Luis Diaz
asked on
SQL: export into csv/sqlcmd method and field config
Hello experts,
I use the following sqlcmd method in order to generate a csv file from a select of an specific table:
sqlcmd -S -d -U -P -i "export_csv.sql" -o "out\out-Temp.csv" -s";" -w 700 -W
export_csv.sql is composed by the following query
However I found an issue concerning the csv generation.
For ProjectID1 values which begin with 0 example: 0123.
In the csv file the value is reported as followed 123 even when I open with a text editor or excel.
My question is the following:
How should I config the ProjectID1 field in order to be able to properly report the values related.
Should I use Cast, Format function withing the select in order to properly generate the values?
If you have question, please contact me.
Thank you in advance for your help.
I use the following sqlcmd method in order to generate a csv file from a select of an specific table:
sqlcmd -S -d -U -P -i "export_csv.sql" -o "out\out-Temp.csv" -s";" -w 700 -W
export_csv.sql is composed by the following query
SET NOCOUNT ON
SELECT ProjectID1,
ProjectName,
WorkPackageID,
WorkPackageExtID,
Name
FROM (
SELECT P.ID as ProjectID1,
ProjectName,
T.WorkPackageID,
WorkPackageExtID,
T.Name,
ROW_NUMBER() OVER(PARTITION BY P.ID, T.WorkPackageID ORDER BY WorkPackageExtID DESC) AS row_num
FROM mstt_schema.Task T
LEFT JOIN mstt_schema.Project P ON P.InternalID = T.ProjectID
WHERE ProjectVersion = 1 and ProjectActive = 1 and MacroScheduleWP = 1 and P.[Version] = 1 and WorkPackageID <> 0
) AS derived
WHERE row_num = 1
However I found an issue concerning the csv generation.
For ProjectID1 values which begin with 0 example: 0123.
In the csv file the value is reported as followed 123 even when I open with a text editor or excel.
My question is the following:
How should I config the ProjectID1 field in order to be able to properly report the values related.
Should I use Cast, Format function withing the select in order to properly generate the values?
If you have question, please contact me.
Thank you in advance for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That's the problem when trying to work out solutions without having actual test data, reference data structure (table structure/data-type) and expected results in the question.
Thanks for the grade and points, though. Have a good day!
Thanks for the grade and points, though. Have a good day!
ASKER
I think you didn't understand my request. The idea was to properly display the 0 when the ID begins to 0.
I found the following solution and it works. with cast and nvarchar.
Here is the revised query:
Open in new window
Thank you anyway for your comment.