SSIS CSV File Failed To Load
-
just to be sure you are indeed producing a Valid CSV
when you define your output file do you have the "text qualifier" set to " (double quote)?
on example below it is not set so the file will not be considered a valid CSV if any of their text fields contain particular characters.
-
thank you for the reply. I tried that and the consuming app still didn't recognize the CSV. I figured out what the problem is but I do not know how to fix it. The problem is as follows...
The consuming app cannot import ".000" b/c that isn't a proper number. If I replace ".000" with "0.000" then the import works. This is super weird. The SS table shows "0" which is correct value for that field. However, the CSV shows ".000". I am not sure why the 0 to the left of the decimal is gone. Below is a table definition:
CREATE TABLE [dbo].[StockTable](
[SYMBOL] [nchar](10) NOT NULL,
[INTERVAL] [nchar](1) NOT NULL,
[DATE] [date] NOT NULL,
[NAME] [nchar](40) NOT NULL,
[OPEN] [numeric](8, 4) NOT NULL,
[HIGH] [numeric](8, 4) NOT NULL,
[LOW] [numeric](8, 4) NOT NULL,
[CLOSE] [numeric](8, 4) NOT NULL,
[VOLUME] [numeric](8, 4) NOT NULL
) ON [PRIMARY]
GOThe problem is the Volume field. Sometimes the value can be 0 and that is totally fine. Below is the SP that copies the table from main table into a one that is formatted to meet the needs of the consuming app.
INSERT INTO DBO.AppStockTable
SELECT'SPX' AS SYMBOL,
'D' AS INTERVAL,
T1.TRADE_DATE AS [DATE],
'S&P 500' AS [NAME],
0 AS [OPEN],
9999 AS [HIGH],
0 AS [LOW],
T1.Calc1 AS [CLOSE],
T1.Calc2 AS VOLUME
FROMDBO.StockTable AS T1From this point on the SSIS takes over. I have a an OLE DB Source to connect to the table and then a Flat File Destination to output to a CSV.
How can I force the value of 0 to be 0 in the CSV and not be .000? The zeros to the right of the decimal are fine. The problem is there is no 0 to the left of the zero.
Any help you can offer is really appreciated.
Thank you
-
here is a picture of what I am seeing the preview option. It has the leading zero missing:
Yet, the OLE DB preview is showing the leading zero:
How do I fix this problem?
-
when generating csv files in SSIS (which I avoid every time I can - powershell easier for it) I normally do the conversion/formatting on the extract sql so that for SSIS all columns come as strings.
I also impose a strict date format to avoid regional settings putting the date on a format that the destination may not recognize.
so your select within your dataflow would be come the following (and never use a table as a source, always a sql statement - that will save you hassle down the line.
-- replace #stocktable with your own table
select SYMBOL
, INTERVAL
, convert(varchar(10), DATE, 121) as [DATE] -- user varchar(26) if its a datetime field
, NAME
, convert(varchar(10), [OPEN]) as [OPEN]
, convert(varchar(10), HIGH) as HIGH
, convert(varchar(10), LOW) as LOW
, convert(varchar(10), [close]) as [close]
, convert(varchar(10), VOLUME) as Volume
from #StockTable
-- if the application receiving the file requires the sign to be on the right of the value (some do) then the following will do it.
select SYMBOL
, INTERVAL
, convert(varchar(10), DATE, 121) as [DATE] -- user varchar(26) if its a datetime field
, NAME
, convert(varchar(10), abs([OPEN])) + iif([OPEN] >= 0, '','-') as [OPEN]
, convert(varchar(10), abs(HIGH)) + iif(HIGH >= 0, '','-') as HIGH
, convert(varchar(10), abs(LOW)) + iif(LOW >= 0, '','-') as LOW
, convert(varchar(10), abs([close])) + iif([close] >= 0, '','-') as [close]
, convert(varchar(10), abs(VOLUME)) + iif(VOLUME >= 0, '','-') as Volume
from #StockTableon a diff note - if your destination application is Excel then before changing code open the file with notepad and see if the leading zero is there or not - Excel will do its own formatting and it may seem wrong without that being the case
-
Thank you so much for the solution. It works perfectly.
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply