Discover import edi file into sql, include the articles, news, trends, analysis and practical advice about import edi file into sql on alibabacloud.com
Currently, You need to retrieve some fields from a data file recorded by row, and store these fields to the Oracle database by row. There are about 700 million data records and about m of raw data files. There are at least two ways to achieve this:
1. Read the original data file into the memory stream, parse a piece of data for each row, and then execute an SQL
Sometimes we may import CSV data to a database table, such as when performing report analysis.I think it's hard to solve this problem at all.ProgramPersonnel! But it would be better if SQL server can complete this task!Yes, SQL Server does have this function.First, let's take a look at the CSV file, which is saved in m
How to use PHP to automate. sql files is to get the contents of the SQL file and then execute each sentence of the SQL statement at once.
code example:
// Read file contents $_sql = file_get_contents ('test.sql'= explode ('; ' New mysqli (db_host,db_user,db_pass); if (My
for importing and exporting Excel from SQL Server, although SQL Server has been given a simpler way to implement it through interactive dialogs, there are sometimes many problems with this approach, such as importing and exporting data. And, for a project, we don't want the implementation of the feature to leave the software program. Therefore, we would like to use a program to implement
files in the Excle table to the SQL Server database. Since SQL Server and Excel are the same parent, they have good compatibility. SQL Server provides a tool to import data directly from an Excel file.
Although this requires the use of intermediate tools to
Tags: daily Baidu f11 SIG SQL structure BFC DFF color htmExport table data and table structure SQL files In the daily development process, it is often necessary to export some of the table data in a database, or the structure of a table, the data to be modified, you need to export the table's SQL structure in the database, including the table statement and data
account 1 = left (bank account, 8), bank account 2 = right (bank account, 8)FromOpenDataSource ('Microsoft. Jet. oledb.4.0', 'Excel 5.0; HDR = yes; IMEX = 2; database = c: \ a.xls'--, Sheet1 $))... [Sheet1 $]
If you want to directly insert and generate a text file, you need to use BCP
Declare @ SQL varchar (8000), @ tbname varchar (50)
-- First import the E
--From the Excel file, import the data into the SQL database, very simply, use the following statement directly:
/*===================================================================*/
--If the table that accepts the data import already exists
INSERT INTO table SELECT * FROM
OPENROWSET (' MICROSOFT. JET. oledb.4.0 '
,
account, 8)FromOpenDataSource ('Microsoft. Jet. oledb.4.0', 'Excel 5.0; HDR = yes; IMEX = 2; database = c: \ a.xls'--, Sheet1 $))... [Sheet1 $]
If you want to directly insert and generate a text file, you need to use BCP
Declare @ SQL varchar (8000), @ tbname varchar (50)
-- First import the Excel table content to a global temporary tableSelect @ tbname = '
/*
* PHP code to export MySQL data to CSV
*
* Sends the result of a MySQL query as a CSV file for download
* Easy-to-convert to UTF-8.
*/
/*
* Establish database connection
*/
$conn = mysql_connect (' localhost ', ' login ', ' pass ') or Die (Mysql_error ());
mysql_select_db (' database_name ', $conn) or Die (Mysql_error ($conn));
mysql_query ("SET NAMES CP1252");
/*
* Execut
database-related operations, I would like to say no more, because these experiences are for almost the entry of friends to see, if you do not know what SQL is a thing, still do not know the basic operation, it is not a pro, this experience really does not suit you oh.
3Create a new Excel to import SQL database table data directly from the Excel Data menu. Sel
is Microsoft. jet. OLEDB.4.0, note that operations on non-SQL Server databases in OPENDATASOURCE (...) use "... ", not". ".(3) import and export data between the SQL Server database and text files.
Import Data
SELECT * INTO text1 FROMOPENDATASOURCE ('Microsoft. JET. OLEDB.4.0 ', 'text; DATABASE = c: "') [data # txt]
E
preceding example, select * From Table1 to ABC can be used in OpenRowSet. while OpenDataSource can only reference Table1, but cannot query table1. Therefore, OpenRowSet is more flexible than OpenDataSource.
2. Use the command line bcp to import and export data
Many large systems not only provide user-friendly graphical user interfaces, but also provide command line control for the system. In addition to using
file and try again.
3) the first row of the imported Exel table must have a column name for each column, as shown in
ID name age
1 tomclus 35
...
If no column name is available
1 tomclus 35
...
Errors may occur.
If not all columns are specified in the preceding example, or select *, errors will occur, such as incomplete columns or data type deployment.
The data import between
only reference table1, but cannot query table1. Therefore, OPENROWSET is more flexible than OPENDATASOURCE.
2. Use the command line BCP to import and export data
Many large systems not only provide user-friendly graphical user interfaces, but also provide command line control for the system. In addition to using SQL statements to operate data in SQL Server, yo
BCP is a command-line tool in SQL Server that is responsible for importing exported data, is based on db-library, and can efficiently import and export large quantities of data in a parallel manner. BCP can export a table or view of a database directly, or it can filter the table or view through a select from statement. When you import export data, you can use de
Excel|server
Basic ways to import/export Excel from SQL Server
/*=================== Import/Export Excel basic methods ===================*/
From the Excel file, import the data into the SQL database, very simply, using the fol
through command lines, but also through SQL. This requires a system stored procedure xp_mongoshell. The preceding command can be rewritten as follows.EXEC master .. xp_mongoshell 'bcp pubs. dbo. authors outC: temp1.xls-c-q-S "GNETDATA/GNETDATA"-U "sa"-P "passWord "'3. Use DTS to import and export dataDTS is the core of data import and export in
original database to the Excle. Some databases, such as Oracle databases, do not support the Excle format. However, we can export it to a CSV file. This type of file Excle can also be opened.
Then, modify the record in Excle. As Excle is a powerful table processing software, it is easier to modify data than to directly modify data in a database. For example, you can use time-based sorting to clear records
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.