SQL Server – Importing Files with Mixed Decimal Separators

Alrighty. So, I had inherited this cube from the original architect. I ended up adding a couple data dimensions, adding to the several that were already there. The is data with many different kind of dates so the Date and Time dimensions play a lot of roles. Apparently, i reach a tipping point. Today, after a co-worker created a smaller version of the cube using the measure group I was having issues with and was able to create aggregations without difficulty, i tried something i had not before. In the aggregation design wizard, i set the all but the most commonly used dimensions to None. Thus, I was asking the wizard to create fewer aggregations. I was able to create aggregations with solid optimization without any difficulty.

Since you are not allowed to script but SQL try the following:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\"');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

There you go, another text file that has the doubled double quotes stripped.

If you want all doubled double quotes replaced with single quotes, do this UPDATE:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\'');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

Now, go import that file (improved_importfile.txt) and have fun with it.

Give it a Try !!!