SQL Import Wizard -- Importing .csv and .tab files

This blog is to let you know how to use the SQL import wizard. It is very interesting. The most important thing to remember is that you can change the data length and type of both the file you are importing from and the length of the fields that you want to import to.

When you first enter into the Wizard, go past the Welcome screen and You come to the

Choose Data Source page.

You can choose many types of files to import, including an Access file, Excel file, or from a database or a .net Data Provider Source. If you know the details of you provider source application name, or know the location of the Excel file, or have access to the Access database, following those steps are all pretty easy.

However, if you want to import a comma spaced value (.csv) or a tab spaced value (.tab) file, you must use the "Flat File Source" option. I suggest using a .tab file if you are transferring data that uses any kind of non-numeric data such as a list of companies or clients information. These types of non-numeric data are likely to contain comma's themselves, and will make the import wizard think that some of the data items themselves belong in different rows.

A .tab file is very easily interepreted by the wizard. However, one caution is if you file is known to contain tabs. If it does, you will have the same problem whereever a tab appears in the data. Your columns will be skewed upon import.

If your data itself contains commas and contains tabs together. Your best bet would be to replace the tabs with a single space and use a .tab file. Otherwise, you could use a different delimiter such as a semicolon(;) or a vertical bar (|).

Next Step:

Select you .tab or .csv file source. In the open file dialog box, be sure to select files of all types where it says "Files of type:" at the bottom. This part of the process was not done well, because the file browser has as a default to show you only files of .txt or text files. However, that will not cause your .tab or .csv files to show up unless you saved them as .txt type.

Next, be sure to mark the type of delimiter being used (i.e. a tab for a .tab file and a comma for a .csv file). If you get this wrong you will have another chance to correct this before importing your file.

Important Options:

On the same page that you chose your file, you will see to the left a series of options, including:

General

Columns

Advanced

Preview

These are very important tools. If you learn them, it will save you hours of time. Upon exporting 19 million rows of records, I spent over 20 hours trying to solve import problems because I did not understand what I am about to explain.

General Tab:

This is where you upload the file. Under Locale the default is English (Ireland). That is fine unless the language is in another language or incorporates characters from another language. Consult someone else for that problem.

Code page is where you specify the basic encoding of the page ANSI - Latin I or US ASCII work the best for most files.

Under Format, you should choose "Delimited"for a .csv or .tab file.

Important: If there is a header row with column names in the file, you should set the value where it says "Header Rows to Skip:" to 1, that way you won't be importing the column names as your first row.

Columns Tab:

Specify the Row Delimiter as probably the {CR}{LF} value and the Column delimiter as the tab{t} for .tab files and comma{,} for .csv files. Here you can also select a semicolon or vertical bar{|} as we discussed earlier.

After you have chosen these values, you should click the Reset Columns button to get a preview of how your data will import. With a .csv file, choosing Row Delimiter: {CR}{LF} and Column Delimiter: tab{t} worked perfectly. Check to make sure your data is lined up correctly, and that the number of columns are correct.

Advanced Tab:

Here is the most important part*** under the advanced tab, you can choose the value for OutputColumnWidth. This is a very critical value. If you choose a value that is less than any of the data in that column, your import will fail. For example, if you choose 50, which is the default, and even one piece of data in that column is 51 characters long, then the data will need to be truncated for the import, and the the import will fail.

Solution: Set this value well above the typical data length for the column. 255 is a good number.

Here you can also change the number of columns, and give them the proper names if you didn't include a header row with the column names.

Preview Tab:

Here, you will see a preview of how your data will import just like in the columns tab.

However this has one important function, Data rows to skip. You can use this function to skip any number of rows you don't want to store. If you are having trouble importing a row at a certain position, just insert all the rows before that position by chopping off that portion of your text file (and keeping a backup of the entire file of course), then import all the rows after that position by skipping it.

Also you can use this function to see a preview for what data at a certain row will look like for importing.

Destination Page

Now click next and you must choose a destination SQL location. You should know the location, username and password.

*Select Source Table and Views Page

Click Next and you will have to select the name for the table you want to import to, or you can choose a table to directly insert data into.

I would not suggest ever inserting data into an existing table in a database. The reason is that you don't know how the import will go. Your best option is to import data into a new table, and then use a sql statement like

""INSERT INTO [EXISTING TABLE NAME] (COLUMN1, COLUMN2, COLUMN3, ..., COLUMN10) SELECT

(CORRESPONDINGCOLUMN1,

CORRESPONDINGCOLUMN2,

CORRESPONDINGCOLUMN3, ...,

CORRESPONDINGCOLUMN10)

FROM [NEWTABLE]"

to insert the data into the existing table. It is much faster to insert data with a sql call than with the export wizard. About 100 times faster, literally.

VERY IMPORTANT: Here is another place where detail is key. Click the Edit Mappings... button, and it will show you the data types and length of each column you are going to insert. If you choose to insert into a entirely new table, which is what I suggest, then you will not have trouble about truncating any data. Just make sure the size of each column is as great as the column widths you specified in the Advanced Tab earlier.

Good luck and happy importing!

A solution to buying a home: The Housing Fund

I believe there is one good way to help this countries citizens, as well as people all over the world, buy homes and keep them. The best system to buy a home is to have a fund of capital, of pure cash, that potential home buyers could tap into. Because home sellers often settle for lower terms if the buyer provides up front cash, home buyers could take a discount on their purchase.

Once the home is purchased, the borrowers of course would be indebted to repay their loan in monthly installments. I admit this is much like a loan, but the key difference is that people would not have to pay interest on this loan. Because of the charity involved in this fund, one would hope that others would pay back this loan as quickly as they can. The Housing Fund would act more like a charity than like a bank. Its purpose: to provide the people of America with the ability to find a home they need, without paying ridiculous amounts that cripple them.

It is in the best interests of the government of this country to do all they can to support the home buyers of America. These people represent fledgling or expanding families mainly.

Admittedly, there may be some who would try to abuse this system. However, people would have to show a justified need to use this fund--that is, to buy one house they live in.

We have seen that the government has agreed to pay billions of dollars in the fall of 2008 to save the housing market, and the corporate banks that were much hurt by the sub-prime loans in the housing industry. How much more useful would that money be to make a housing fund that helped people buy homes for 90% of their market value in cash, and then pay back that loan without interest. With enough people paying off their loans, the fund would remain at a relatively level sum. Additionally, it is hoped that kind and charitable people of all sorts, including those who benefit directly from the fund, would contribute more than their required payments in the years once they have payed off their loan.

Simply put, we need a way to help the struggling people of America to buy their home. Imagine the relief that will ensue when thousands of Americans have paid off their mortgages in 5 years instead of 30. This will free up consumer spending in other areas, and it will allow many Americans to see the benefit of such a system. The bottom line is this: it is not simply a good thing for America to subsidize the housing market through a national charity, it is essential.

In order to offset the cost of inflation, and keep the fund at a level that will provide continual benefit to the home buyers of this country, The Housing Fund will need yearly donations from the US government, and other wise individuals (who see the mutual benefits such a system provides to all of us). America needs home owners, families in modestly sized homes that meet their needs, and citizens free from gargantuan burdens. America needs a way to support those who truly need a home here.

Because the people must pay off their loans, even without interest, it will still encourage them to buy a home they can afford and not abuse the system. They are encouraged, and watched, so that they buy a home within their means. This will do more to help America than social security for the families in those homes. Imagine thousands without housing payments to make, but at the same level of income! Imagine parents free from mortgages when their kids are still young! Many will undoubtedly give back to this Fund out of gratitude, and those that don't will wish they had!

Marraige and Family

Tonight I attended a conference hosted by The Church of Jesus Christ of Latter-day Saints. It's topic was the protection of the institution of the family, and its importance to the world. The impetus for such an event was the recent call for a redefinition of marriage in California, entitled Proposition 8.

If such a proposition were passed, it would allow people of the same gender to be married and given equal protection under the law as any other marriage. To be short, it would upset the very foundation upon which the traditional society rests, and it would escalate into various forms of pro-same-sex marriage teachings and initiatives in schools and communities throughout California.

As one who one day wishes to return to California to live for part or all of the remainder of life (my wife permitting =) ), I hope that this state, instead of being a hotbed of anti-traditionalist tension and teaching, might remain a stalwart bastion of hope for all those who hold conservative family values. I would hope our whole nation would rethink its stance on same-sex marriage, and how we are to uphold our values if we give such a practice equal rights under the law.

All of this tension, and all of this redefining our values bespeaks a larger push by the enemy of all mankind, even Satan, to disrupt our lives and make it harder for God's good grace to shine down upon us. In the final day, it will be us who will be held responsible for the errant ways of many in California and elsewhere if we do not do all we can to support traditional marriage and family roles. It will be our own children who will suffer for this.