Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

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

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

Open in new window


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
Avatar of Nakul Vachhrajani
Nakul Vachhrajani
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Luis Diaz

ASKER

Thank you very much for your feedback.
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:

SET NOCOUNT ON

SELECT ProjectID1,
    ProjectName,
    WorkPackageID,
    WorkPackageExtID,
    Name
FROM (
    SELECT CAST(P.ID AS NVARCHAR) 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

Open in new window


Thank you anyway for your comment.
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!