ssrs Datasource CSV semi colon delimited giving long column name

Question

I have a tool that generates csv automatically but in semicolon ;however when I run the query in SSRS it is giving only one column to all the fields in the report. When I changed the semicolon with comma, it is working well.
I want to modify or change the settings in the SSRS to give the correct format. I run the query for example the csv has sample1;sample2;sample3;;;; a;b;c; , the output is giving sample1;sample2;sample3;;;; a;b;c;

I tried changing the reportingservices.config this way but not working

 <Extension Name="Semicolon" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
                <OverrideNames>

    <Name Language="en-US">CSV (semicolon delimited)</Name>
                 <Name Language="en-EN">CSV (semicolon delimited)</Name>


                </OverrideNames>
                <Configuration>
                    <DeviceInfo>
             <FileExtension>csv</FileExtension>
                        <FieldDelimiter>;</FieldDelimiter>
<SuppressLineBreaks>True</SuppressLineBreaks>
                    </DeviceInfo>
                </Configuration>
            </Extension>

I want this kind of output.

sample1 |  sample2 | sample3
     a          b         c

Any idea on how to do this in SSRS.

Just curious.. did you restart the RS services after the change? — Harry, Jun 01 '18 at 00:05
Just restarted the services again this morning and also enable protocol fot TCP/IP on protocol for SQL Server. Reporting Services is working now, but the semicolon csv is not in the right format — great77, Jun 01 '18 at 10:10
This is all very confusing. What are you _viewing_ this file in? What are you _generating_ this file in? — Nick.Mc, Jun 01 '18 at 10:13
@Nick. I am using SSRS to connect to csv and run query. It is a semi colon csv file, Because is a tool that generate it automaticall, I can not just change manually everytime the tool is running to comma delimited file. Check the screen shot added — great77, Jun 01 '18 at 10:19
So.... really you are connecting to a _database table_ from SSRS. And that database table is populated from a semicolon delimited file. And you can't change the program that generates the file. How are you importing the file into the database? You've missed out some really fundamental things in your explanation — Nick.Mc, Jun 01 '18 at 10:20
We are not importing files to DB. I have already connected it through ODBC and tested succesfully. When I changed the semicolon with comma in the file and it is working good. However, I do not want comma delimited file. What do you think? Is there no way to handle semicolon this way.I am not expert in this........ what can I do ? — great77, Jun 01 '18 at 10:34
If using ODBC to connect to the semicolon separated file.. then can you not specify semicolon in ODBC as your separator rather than the comma? — Harry, Jun 01 '18 at 22:06
How ? I have no idea. Please tell me how. Is it in the odbc settings — great77, Jun 01 '18 at 22:19
I'm only going by the comment you made above.. When setting up the ODBC to your data source, you should be able to define the separator (Define Format). Unless I'm wrong.. haven't had to do this in a while! — Harry, Jun 01 '18 at 22:49
I have decided to just write a batch file to replace the semicolon immediately the csv is generated by the .exe application. The example to replace is also in this link : https://stackoverflow.com/questions/23075953/batch-script-to-find-and-replace-a-string-in-text-file-without-creating-an-extra/23076141?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — great77, Jun 02 '18 at 10:27

score 0 · Answer 1 · answered Jun 02 '18 at 10:29

0

I have decided to just write a batch file to replace the semicolon immediately the csv is generated by the .exe application. The example to replace is also in this link : Batch script to find and replace a string in text file without creating an extra output file for storing the modified file

@echo off
setlocal enabledelayedexpansion




::  rem   editing and replacing  a text file without redirecting the output to file 

@echo off 
    setlocal enableextensions disabledelayedexpansion

    set "search=%;"
    set "replace=%,"

    set "textFile=try2.csv"

    for /f "delims=" %%i in ('type "%textFile%" ^& break ^> "%textFile%" ') do (
        set "line=%%i"
        setlocal enabledelayedexpansion
        >>"%textFile%" echo(!line:%search%=%replace%!
        endlocal
    )
pause

answered Jun 02 '18 at 10:29

great77

143
4
20

1

You should b able to define a custom delimiter in the schema.ini file. https://learn.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-2017 – Nick.Mc Jun 02 '18 at 11:06
@Nick. That is interesting. where is the schema.ini located in the folder. I read the page now ! – great77 Jun 02 '18 at 11:47
Ok. I have read it now. I have to create schema.ini and define what I want. should i use Format=Delimited(custom character) as the fomat type since this is semicolon – great77 Jun 02 '18 at 12:00

ssrs Datasource CSV semi colon delimited giving long column name

1 Answers1