Download secured roll see (part 1) for how to download details

 

 

 

 

 

 

Import secure-roll text file into SQL server table

SBC means San Bernadino County,  COV means City of Victorville

secured-roll text file( SB county)  to secured-roll table to SBC_POD table to COV_POD table

 

 

1.backup previous table SBC_POD, COV_POD,  Secured-roll table

    1.1 create a new empty table SBC_POD_2023, COV_POD_2023,  Secured-roll_2023 

       

 

1.2 open old table schema 

 

 

 1.3 select all columns and click copy

 

1.4 go back to new table, must click first row first, then click paste button to paste all column here. If not click first row, directly paste will cause error.

 

 

1.5 click save button and type new table name respectively  SBC_POD_2023, COV_POD_2023,  Secured-roll_2023 

 

1.6 click new query, type sql statement to copy all data from original table SBC_POD, COV_POD,  Secured-roll to backup table respectly SBC_POD_2023, COV_POD_2023,  Secured-roll_2023 

 

 

 

2. empty data only, keep table schema, means keep table columns structure, but clear all data inside. For table SBC_POD, COV_POD,

 

3.  Delete table secrued-roll, since I will re-create it later by import task.

 

3.1 reset object id for SBC_POD table back to 1

 

 

 

4. import secured-roll text file 

    4.1 import flat file

   

 

    4.2 select flat file source and type new table name 

 

    4.3 click preview data

 

    4.4 open backup table, secured-roll_2023 table, open design, show all column. Also click Modify Columns, must modify column name exactly same as backup table, such as remove "_" in column name, replace it with space key,  allow null for all column, Data type do not need to match to original backtable.

 

4.5 click finish

 

4.6 failed eror

4.7 try to match data type as well

4.8 still getting error,

 

 

5 open text file in excel

5.1 use excel to open text file, set delimited

 

 

 

5.2 set delimiter as "~"

 

5.3

5.4 save as excel format

 

 

 

5.5 importa data

 

5.6 set excel as source, 

5.7 choose Excel 2007-2010

 

 

5.8 error

 

 

6 download and install driver. 64-bit version of the 'Microsoft Access Database Engine 2010 Redistributable' that will allow you to use the 'Microsoft.ACE.OLEDB.12.0' provider is available here:
http://www.microsoft.com/en-us/download/details.aspx?id=13255

Beause my office is 32-bit, so I install 32-bit. 

 

7 select SQL as destination

8. error, missing driver

 

 

9. download OLE DB driver 

https://learn.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver16

 

10.warning:  user "sa" does not authorized to access OLE DB driver, so must change connection type from SQL server authentication to Windows authentication with my windows user name and password

 

11. test connection until you see success

by

Please log in or register