Hot answers tagged csv-file
9
I see that you clearly stated your looking for a solution in SSMS but I thought I would provide a PowerShell solution in case it helps. (SQLPS is accessible from inside of SSMS 2008 & SSMS 2008R2)
You can use SQLPS (or regular PowerShell with the SQL cmdlet snapin) to run something like this:
Invoke-Sqlcmd -Query "sp_databases" -ServerInstance ...
8
You must create a schema.ini file containing the delimiter in the same directory as the text file you are opening. This is the only way to override the registry values on a per-file basis. See the file format documentation on MSDN. Example:
SELECT *
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Text; HDR=YES; Database=C:\Text',
'SELECT * FROM ...
7
If you have your first CSV loaded into a table, you can just as easily load the other one into a staging table (presumably with the same structure as the 'real' one). Then you can get the new rows by
SELECT * FROM staging_table
EXCEPT
SELECT * FROM real_table
;
Rows missing from the new CSV can be get reversing the two sides around EXCEPT. However, ...
4
It could be normal if there are newlines in certain text fields. Newlines are allowed when the value in the field is enclosed by double quotes.
And obviously that makes the number of lines in the file greater than the number of records.
Example :
$ cat file.csv
1,"ab
cd",2
3,"efgh",4
$ wc -l file.csv
3 file.csv
=> create table csvtest(a int, b text, c ...
4
You tell the COPY command to look for commas as delimiters (DELIMITERS ','), but there are no commas in your CSV. Use the 'text' format instead (it's the default, so you don't have to specify it) and do not specify a delimiter:
The default is a tab character in text format.
(source)
3
drwxr--r-- 6 peter peter 4096 2011-04-14 14:03 phm
postgres@dexter:/home/peter/PyPacks$ cd phm
bash: cd: phm: Permission denied
Directories need to be executable to be able to cd into them (or use files within them). All the sub-directory from / to the /home/peter/PyPacks/phm need to be executable by the user you want to use for this to work.
Try at ...
3
If you like the PowerShell script approach, I have a script that Exports to CSV from SSMS via PowerShell. I like it in so far you can have a dynamic SQL Script, heck you can select whatever text and SSMS passes it to the script as an argument.
Only downside is I haven't found a clever way to pass along the current window's connection. My current ...
2
This question was answered on Stackoverflow.
By default its one transaction per
disk rotation (roughly),
http://www.sqlite.org/faq.html#q19
The way to speedier writes is to wrap
multiple ones inside a transaction.
2
I know it would be easier to find something pre-built, but you could write a small application in your favorite language to handle the simple task of asking the database for the data and writing it to a file. For this you should take advantage of the suggestion from Jack Douglas to modify the reporting procedures to create CLOBs. Your app could then ...
2
REPLACE mechanically runs DELETE and INSERT. That may change the PRIMARY KEYs.
Here is something else you can do.
Suppose your table is called name_city and it looks like this:
CREATE TABLE name_city
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL,
PRIMARY KEY (id)
);
and you want to do the LOAD ...
2
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 ...
2
I also wrote an article about comparing two data sets using Excel, text editor or database engine. For your volume of data it is clear that, from the three options, only the database engine would be suitable.
Unless you use some SSIS feature (I don't have any knowledge about SSIS), then you have to compare the two data sets after they are loaded in the ...
2
You could email them with UTL_MAIL or POST them to another server with UTL_HTTP
Alternatively, save the complete reports as a clob in a table and query from any client - this is a little more work as you'll have to rewrite your reporting procedures, but is still easier than starting from scratch on the client.
1
According to the documentation, you can use SET statements to transform the data on the way in.
[SET col_name = expr,...]
The expr expression can include the column name, which will be interpreted as the data being read from the file and destined for that column... so, for example, at the end of your LOAD DATA INFILE statement you might use:
SET ...
1
Another answer has already been accepted, but I disagree with that answer, because your question first requires an understanding of the meaning of the term "unsafe" and the phrase "considered unsafe" when it is used related to MySQL replication.
When speaking of the “safeness” of a statement in MySQL Replication, we are referring to whether a statement ...
1
Is it possible to use a stored procedure to read the file inside the blob column, loop on each line and insert the data in a table?
Definitely.
However, I would consider rethinking this plan.
A SQL Server database generally isn't a great place to be storing BLOBs, particularly given that you're just going to turn around and process them into row data ...
1
It really depends which constraints are violated. You neglected to disclose that in your question.
To disable triggers (including foreign key constraints):
ALTER TABLE tbl DISABLE TRIGGER ALL;
Be sure to revert it afterwards:
ALTER TABLE tbl ENABLE TRIGGER ALL;
More in the manual.
However, this does not disable Check, Not-Null, Unique, Primary Key or ...
1
I would suggest you to make use of External Tables. You can create an external table on your CSV file using ORACLE_LOADER driver and then update your existing table with data in your external table using DML (MERGE for example).
Consult Oracle Utilities Guide for detailed info.
What follows is my sample of how you can update tables from flat files.
First ...
1
Both the approaches already suggested appear to be unnecessarily complicated.
Just use psql's built-in \copy command, which works just like server-side COPY but does a copy over the wire protocol to the client and uses client paths.
Because it's a psql backslash command you omit the trailing semicolon, eg:
\copy products TO '/tmp/products.csv' CSV ...
1
Use TO STDOUT instead of TO filename and redirect the output to a local file.
If you are in psql, first specify the output file with
\o nice_file_name.csv
then do the COPY:
COPY products
TO STDOUT
DELIMITER ',';
This will hopefully create the file and populate it with all products data.
This solution is applicable if using psql interactively, ...
1
Load the CSV file into a "staging" table. From there you can easily do the UPDATE/INSERT/DELETE in a single statement for each one
Something along the lines:
update real_table
set ...
where exists (select 1 from staging_table where ...)
insert into ...
select
from stage_table
left join real_table
where real_table.some_col is NULL
delete real_table
...
1
in general, you should use \copy where you can in psql instead of COPY since that wraps COPY FROM STDIN. Where you want to copy from a file, you can wrap that yourself in your own application (specifics dependent on language but see the Pg docs).
The alternative is to put files somewhere that the Postgres user can access them, like /tmp/ and COPY from ...
1
Since MySQL 5.1, there has been a native tool called MySQL for Excel.
It is bundled along with MySQL for Visual Studio, a bunch of connectors, sample databases and so forth.
The MySQL Documentation has a webpage for
MSI Installation
Editing
Importing
Appending
Exporting
There is also a forum for users of this product (As of this posting, there are 5 ...
1
We implemented a pretty simple C# command-line program that acted as a pre-parser before bulk inserting. It worked quite fast and you could create something with the free Visual Studio Express (or even with just the compiler, I imagine).
To be clear, you would only potentially need Visual Studio to CREATE the program (you can do it without Visual Studio, ...
1
You might have to check the last character of each line
Instead of this:
LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'
INTO TABLE players
FIELDS TERMINATED BY ','
ENCLOSED BY '^'
LINES TERMINATED BY '\n';
Try this:
LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'
INTO TABLE players
FIELDS TERMINATED BY ','
ENCLOSED ...
1
I would do this in Excel - much easier!
Give the following assumptions...
ALWAYS two items within the "field_number" field
information sorted by "id,lead_id,field_number"
Rename cells D1 and E1 as "field_1" and "field_2"
Then use the following formulae;
In cell E2; "=D2 & ' ' & D3"
In cell E3; "" (leave blank)
Then copy cells "E2:E3", and ...
1
This will generate a CSV per lead_id in order of field_number.
SELECT lead_id, lead_id, GROUP_CONCAT(value)
FROM test_table
GROUP BY lead_id
ORDER BY field_number
INTO OUTFILE '/tmp/result.txt'
FELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
There are 2 lead_id columns to match your output. MySQL doesn't have ROW_NUMBER() to ...
1
It would probably be overkill, but you could install Oracle Express Edition and use database links to have it retrieve the data to be written from the remote database. You would probably want the logic to remain on the existing server and just have the XE do the file writing. This method would allow you to re-use almost all your code, but it has the ...
Only top voted, non community-wiki answers of a minimum length are eligible
