I am working in a dataware house and few things are not clear to me that is why I wanna discus here.

For example, I have 10 MS Access files, and each of they have more than 5000658 number of rows, and data is in such a format like
Plz check attached image currentState.png

Whereas I have to import this large amount of data into MSSQL which can support large amount of data, where as MS access can support only up to 2 GB. And when I will import all of the data into MSSQL then total number of rows will be 50006580, then I have to do indexing to speed up database, otherwise it could take couple of mints for seaching.

But I thing before that I will do indexing I need to bring this data into the correct order otherwise end results will not be correct. And according to the my knowledge I need data in such a format like
Plz check attached image requireState.png

So now plz tell me that what to do in this situation how to bring data in correct format, so that it will give right answer when I will run quires in MSSQL.

Thank

Recommended Answers

All 6 Replies

Member Avatar for LastMitch

So now plz tell me that what to do in this situation how to bring data in correct format, so that it will give right answer when I will run quires in MSSQL.

It's the weekend. I doubt if someone have MSSQL to test a query.

What query do you have so far or what format are you trying to created. Any samples? Just to get an idea what you are doing?

to know about required format plz check attached image of table. and i am atucally working in data ware house.

Member Avatar for LastMitch

to know about required format plz check attached image of table. and i am atucally working in data ware house.

I don't understand your situation.

I feel you are not asking or doing a query now but you want to know how to speed up the query meaning getting the data from the database quickly?

Is that what you want to do?

dear brother
actually i have explained it in first post, i know how to speed up query for that purpose i need to do proper indexing. for now, i want to actually data is misplaced in column like data of "Name" column is in "Address" or "Date" column. so i want to put data in their original column.
to get better understanding of my problem, plz check both attached images in my first post where i have told you all the details.
let me try to explain it again
for example, i have tables in MS Access files in such form,

Note:- Plz ignore '-' character, that is just to create gap

Name----Address----MobileNumber-----CNIC-----Date
Abc--------Xyz-----------123---------------456-------10-5-12
Xyz--------123-----------Abx-------------10-5-12----456
456--------Abc---------10-5-12-----------123--------Xyz
10-5-12--456-----------Xyz----------------123-------Abc

so now you can see that data is misplaced in different column not in their original column
so now i want to bring back data into their original column for example

Note:- Plz ignore '-' character, that is just to create gap

Name----Address----MobileNumber---CNIC----Date
Abc------Xyz--------------123-------------456-----10-5-12
Abc------Xyz--------------123-------------456-----10-5-12
Abc------Xyz--------------123-------------456-----10-5-12
Abc------Xyz--------------123-------------456-----10-5-12

like all these names in table into the column of name, and all these address into the column of address.

I dont see how you are going to fix this programatically. I dont even see a pattern here.

I know this sounds cray, but what I would do is export this into a spreadsheet (Excel) and try to group the rows into sections that "match" There should only be 5 groups because you have 5 columns. One of the groups is going to be correct. So you only have to work on 4 groups of problem records. You should have about 11k rows per section. Since Excel has excellent sorting ability, after I get each section grouped together, then you can use cut, copy, paste to fix each section very quickly. Once I have all 4 sections fixed, get the file and use it to re-upload to a tabel in your DB (MSSQL or MySQL).

Again, I know that it sounds like a lot because you have 56k records, but I see this as 5 groups of chunks of data.

Any other solution….?
Because it’s quite difficult and lengthy and very soon I will have more records of 230 million number of rows. So I will not be able to so that much lengthy works.
Can some body suggest me any better solution.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.