How to import text, Excel, and Access data to SQL Server

As follows:
Copy codeThe Code is as follows:
"AWU", "102300", "ZX240-3", "2609997000", "2609997000", "3016924000", "", "3091775000 ","","", "QCR0000285 ",""
"AYE", "001800", "ZX60", "2609997000", "2609997000", "3016924000 ","","","","","", ""

Although the project has developed applications, parses similar text files, and stores records in the database, the efficiency is obviously less than that of batch import. To save time, starting from PM, I decided to import the text file to Excel first, and then import the Excel file to SQL Server. (Because some of the previous backup work is the inverse method of this operation ).

Open Excel2007, and then select to open the text file of the data source. The system automatically enters the "text import wizard ".
Step 1, select the field segmentation method, this example uses the "segmentation symbol", adjust the text file encoding mode (UTF-8, ANSI, etc.), it is guaranteed that the imported data is not garbled.
Step 2: select the separator and text recognition symbol (Great! How to remove double quotation marks ).
Step 3: Set the data type (General, text, and date) for each column ). Click Finish!
Two details are prompted:
1. for example, if the second field of the second row of the sample data is "001800", if you follow the "regular" type in the third step of the import, the front-end "00" will be removed, similar to the number processing method, if you want to retain it, select the "text" type.
2. Sometimes there are inevitable format problems in the source data, especially when importing database forms with strong constraints, an error is reported. In this case, you have to check the Excel data based on the error message. During the test, the comma appears inside the "," Double quotation mark, causing data to be unable to be imported. Multiple double quotes: "" and so on.
3. After data is imported into Excel, it is best to insert a row before the first row and enter the field name of the corresponding form of the database. We do not know whether this trip can be implemented. Please prove it in practice :)

Therefore, we recommend that you first import text files to Access and import them to SQL Server through Access. The advantage is that the data type of Access is more similar to that of SQL Server. In this way, when a text file is imported into Access, the validity of the data is strictly verified to avoid a lot of trouble in the future.

The import method is similar. For a brief introduction, open Access2007 and select "external data"-"Text File". After selecting a text file, the "Import text wizard" appears ".
Step 1: select a fixed width or separator.
Step 2: select the delimiter type and text identifier, and select "the first line contains the field name ".
Step 3: Set the field name and field type (the most valuable one ).
Step 4: whether to create a primary key.
Step 5: complete!
A detailed question is prompted:
Some text files may contain garbled characters at the front end of the first line during Access import and preview. It doesn't matter. Open source text files, press enter at the beginning of the line, and create an empty line. After the data is imported, the first row of records is deleted again in the Access table.
Finally, use the SQL Server Enterprise Manager to select the form to be re-imported, right-click to select import data. Continue to the next step: SQL Server will strictly match the data type of the record when importing data. If the data type does not match, the data import of this batch will fail.
OK. share these items first. For more tips, please join us. The active use of the Office series and the clever use of the MS software are really powerful, and you need to mine them slowly :) more SQL Server data import skills, I also need to learn more!