|
Splitting/Combing data question
After using the Text Import wizard, the bulk of the data is
in an acceptable format, but I'm left with this type of data, split across columns: In A2: 126 Prices for Meas B2: sured Work C2: s - Major W D2: orks A3: D G B3: ROUNDWORKS Corrected, the result is: A B 2 126 Prices for Measured Works - Major Works 3 D GROUNDWORKS I can locate these records using If IsNumeric(Left(C.Value, 4)) Then but I don't know how to proceed further. Any pointers would be much appreciated. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 |
Splitting/Combing data question
Hi Jason
some questions: - How do you indentify a new column. That is why do you insert '125' and 'D' in column A. - Does the source data has a amximumn column number or how do you identify the last column (the last non-blank?) So you may explain the underlying algorithm for this with a little bit more detail :-) Otherwise this macro would be quite straightforward. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: After using the Text Import wizard, the bulk of the data is in an acceptable format, but I'm left with this type of data, split across columns: In A2: 126 Prices for Meas B2: sured Work C2: s - Major W D2: orks A3: D G B3: ROUNDWORKS Corrected, the result is: A B 2 126 Prices for Measured Works - Major Works 3 D GROUNDWORKS I can locate these records using If IsNumeric(Left(C.Value, 4)) Then but I don't know how to proceed further. Any pointers would be much appreciated. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 |
Splitting/Combing data question
I chose Delimited.
I, also, could see no logic. The Import Wizard allows a line at which to commence import. Does it allow a line at which to end the import? Perhaps then I could import the data in blocks which would "fit" the column settings. Is there an alternative to the Import Wizard? Regards and Thanks. "Frank Kabel" wrote in message ... Hi Stuart IMHO the only way to correct this is playing with the text import settings. what setting did you choose? - fixed lenght - delimiters? Looking at you data examples there is no logic I can imagine to distinguish good/bad data. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: The data is in the pdf file format, and the only way I know to import this is via the Text Import wizard. That is how I set up the columns. It works for about 50% of the data........not the rest. There are about 20,000 rows of data !! Some of the blocks of data do not align correctly with the result that a column set for one block of data 'splits' data in another block eg: | 0.25 | 55 | is okay, but further down in another block I'd get 0|.25 | 55 | Here's 'good' data: 10.25 153.78 1.2 85 and now 'bad' 8.75 158.99 78.34 5 doesn't align. Regards and thanks. "Frank Kabel" wrote in message ... Hi Jason some questions: - How do you indentify a new column. That is why do you insert '125' and 'D' in column A. - Does the source data has a amximumn column number or how do you identify the last column (the last non-blank?) So you may explain the underlying algorithm for this with a little bit more detail :-) Otherwise this macro would be quite straightforward. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: After using the Text Import wizard, the bulk of the data is in an acceptable format, but I'm left with this type of data, split across columns: In A2: 126 Prices for Meas B2: sured Work C2: s - Major W D2: orks A3: D G B3: ROUNDWORKS Corrected, the result is: A B 2 126 Prices for Measured Works - Major Works 3 D GROUNDWORKS I can locate these records using If IsNumeric(Left(C.Value, 4)) Then but I don't know how to proceed further. Any pointers would be much appreciated. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
Splitting/Combing data question
Hi Stuart
you said that the source file is a PDF document. Maybe you can use Acrobat Distiller to export the PDF file to a different format and use this file as import source. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: I chose Delimited. I, also, could see no logic. The Import Wizard allows a line at which to commence import. Does it allow a line at which to end the import? Perhaps then I could import the data in blocks which would "fit" the column settings. Is there an alternative to the Import Wizard? Regards and Thanks. "Frank Kabel" wrote in message ... Hi Stuart IMHO the only way to correct this is playing with the text import settings. what setting did you choose? - fixed lenght - delimiters? Looking at you data examples there is no logic I can imagine to distinguish good/bad data. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: The data is in the pdf file format, and the only way I know to import this is via the Text Import wizard. That is how I set up the columns. It works for about 50% of the data........not the rest. There are about 20,000 rows of data !! Some of the blocks of data do not align correctly with the result that a column set for one block of data 'splits' data in another block eg: | 0.25 | 55 | is okay, but further down in another block I'd get 0|.25 | 55 | Here's 'good' data: 10.25 153.78 1.2 85 and now 'bad' 8.75 158.99 78.34 5 doesn't align. Regards and thanks. "Frank Kabel" wrote in message ... Hi Jason some questions: - How do you indentify a new column. That is why do you insert '125' and 'D' in column A. - Does the source data has a amximumn column number or how do you identify the last column (the last non-blank?) So you may explain the underlying algorithm for this with a little bit more detail :-) Otherwise this macro would be quite straightforward. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: After using the Text Import wizard, the bulk of the data is in an acceptable format, but I'm left with this type of data, split across columns: In A2: 126 Prices for Meas B2: sured Work C2: s - Major W D2: orks A3: D G B3: ROUNDWORKS Corrected, the result is: A B 2 126 Prices for Measured Works - Major Works 3 D GROUNDWORKS I can locate these records using If IsNumeric(Left(C.Value, 4)) Then but I don't know how to proceed further. Any pointers would be much appreciated. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
Splitting/Combing data question
I only have Acrobat Reader 6.0 (obviously FreeWare)
Is Distiller freeware? This is hopefully a one-off import, so I'd rather avoid the purchase of new software. Regards. "Frank Kabel" wrote in message ... Hi Stuart you said that the source file is a PDF document. Maybe you can use Acrobat Distiller to export the PDF file to a different format and use this file as import source. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: I chose Delimited. I, also, could see no logic. The Import Wizard allows a line at which to commence import. Does it allow a line at which to end the import? Perhaps then I could import the data in blocks which would "fit" the column settings. Is there an alternative to the Import Wizard? Regards and Thanks. "Frank Kabel" wrote in message ... Hi Stuart IMHO the only way to correct this is playing with the text import settings. what setting did you choose? - fixed lenght - delimiters? Looking at you data examples there is no logic I can imagine to distinguish good/bad data. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: The data is in the pdf file format, and the only way I know to import this is via the Text Import wizard. That is how I set up the columns. It works for about 50% of the data........not the rest. There are about 20,000 rows of data !! Some of the blocks of data do not align correctly with the result that a column set for one block of data 'splits' data in another block eg: | 0.25 | 55 | is okay, but further down in another block I'd get 0|.25 | 55 | Here's 'good' data: 10.25 153.78 1.2 85 and now 'bad' 8.75 158.99 78.34 5 doesn't align. Regards and thanks. "Frank Kabel" wrote in message ... Hi Jason some questions: - How do you indentify a new column. That is why do you insert '125' and 'D' in column A. - Does the source data has a amximumn column number or how do you identify the last column (the last non-blank?) So you may explain the underlying algorithm for this with a little bit more detail :-) Otherwise this macro would be quite straightforward. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: After using the Text Import wizard, the bulk of the data is in an acceptable format, but I'm left with this type of data, split across columns: In A2: 126 Prices for Meas B2: sured Work C2: s - Major W D2: orks A3: D G B3: ROUNDWORKS Corrected, the result is: A B 2 126 Prices for Measured Works - Major Works 3 D GROUNDWORKS I can locate these records using If IsNumeric(Left(C.Value, 4)) Then but I don't know how to proceed further. Any pointers would be much appreciated. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
Splitting/Combing data question
Hi
no Acrobat Distiller is (unfortunately) no freeware. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: I only have Acrobat Reader 6.0 (obviously FreeWare) Is Distiller freeware? This is hopefully a one-off import, so I'd rather avoid the purchase of new software. Regards. "Frank Kabel" wrote in message ... Hi Stuart you said that the source file is a PDF document. Maybe you can use Acrobat Distiller to export the PDF file to a different format and use this file as import source. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: I chose Delimited. I, also, could see no logic. The Import Wizard allows a line at which to commence import. Does it allow a line at which to end the import? Perhaps then I could import the data in blocks which would "fit" the column settings. Is there an alternative to the Import Wizard? Regards and Thanks. "Frank Kabel" wrote in message ... Hi Stuart IMHO the only way to correct this is playing with the text import settings. what setting did you choose? - fixed lenght - delimiters? Looking at you data examples there is no logic I can imagine to distinguish good/bad data. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: The data is in the pdf file format, and the only way I know to import this is via the Text Import wizard. That is how I set up the columns. It works for about 50% of the data........not the rest. There are about 20,000 rows of data !! Some of the blocks of data do not align correctly with the result that a column set for one block of data 'splits' data in another block eg: | 0.25 | 55 | is okay, but further down in another block I'd get 0|.25 | 55 | Here's 'good' data: 10.25 153.78 1.2 85 and now 'bad' 8.75 158.99 78.34 5 doesn't align. Regards and thanks. "Frank Kabel" wrote in message ... Hi Jason some questions: - How do you indentify a new column. That is why do you insert '125' and 'D' in column A. - Does the source data has a amximumn column number or how do you identify the last column (the last non-blank?) So you may explain the underlying algorithm for this with a little bit more detail :-) Otherwise this macro would be quite straightforward. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: After using the Text Import wizard, the bulk of the data is in an acceptable format, but I'm left with this type of data, split across columns: In A2: 126 Prices for Meas B2: sured Work C2: s - Major W D2: orks A3: D G B3: ROUNDWORKS Corrected, the result is: A B 2 126 Prices for Measured Works - Major Works 3 D GROUNDWORKS I can locate these records using If IsNumeric(Left(C.Value, 4)) Then but I don't know how to proceed further. Any pointers would be much appreciated. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
Splitting/Combing data question
Thanks for your help and suggestions.
Regards. "Frank Kabel" wrote in message ... Hi no Acrobat Distiller is (unfortunately) no freeware. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: I only have Acrobat Reader 6.0 (obviously FreeWare) Is Distiller freeware? This is hopefully a one-off import, so I'd rather avoid the purchase of new software. Regards. "Frank Kabel" wrote in message ... Hi Stuart you said that the source file is a PDF document. Maybe you can use Acrobat Distiller to export the PDF file to a different format and use this file as import source. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: I chose Delimited. I, also, could see no logic. The Import Wizard allows a line at which to commence import. Does it allow a line at which to end the import? Perhaps then I could import the data in blocks which would "fit" the column settings. Is there an alternative to the Import Wizard? Regards and Thanks. "Frank Kabel" wrote in message ... Hi Stuart IMHO the only way to correct this is playing with the text import settings. what setting did you choose? - fixed lenght - delimiters? Looking at you data examples there is no logic I can imagine to distinguish good/bad data. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: The data is in the pdf file format, and the only way I know to import this is via the Text Import wizard. That is how I set up the columns. It works for about 50% of the data........not the rest. There are about 20,000 rows of data !! Some of the blocks of data do not align correctly with the result that a column set for one block of data 'splits' data in another block eg: | 0.25 | 55 | is okay, but further down in another block I'd get 0|.25 | 55 | Here's 'good' data: 10.25 153.78 1.2 85 and now 'bad' 8.75 158.99 78.34 5 doesn't align. Regards and thanks. "Frank Kabel" wrote in message ... Hi Jason some questions: - How do you indentify a new column. That is why do you insert '125' and 'D' in column A. - Does the source data has a amximumn column number or how do you identify the last column (the last non-blank?) So you may explain the underlying algorithm for this with a little bit more detail :-) Otherwise this macro would be quite straightforward. -- Regards Frank Kabel Frankfurt, Germany Stuart wrote: After using the Text Import wizard, the bulk of the data is in an acceptable format, but I'm left with this type of data, split across columns: In A2: 126 Prices for Meas B2: sured Work C2: s - Major W D2: orks A3: D G B3: ROUNDWORKS Corrected, the result is: A B 2 126 Prices for Measured Works - Major Works 3 D GROUNDWORKS I can locate these records using If IsNumeric(Left(C.Value, 4)) Then but I don't know how to proceed further. Any pointers would be much appreciated. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 01/03/2004 |
| All times are GMT +1. The time now is 08:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com